Forum Discussion
Manda13130
Mar 27, 2023Copper Contributor
formula help
Hi
I create the roster at work and stuck on how to create a formula
I'm fine the basic COUNTIFS, but we are introducing 12 hours shifts,
I have E for early
L for Late
N for night
What I'm adding is E12 & N12
E12 easy enough to be included in the count of all the rostered E's
=COUNTIF(E1:E72,"E")+COUNTIF(E1:E72,"E6")+COUNTIF(E1:E72,"I/C")+COUNTIF(E1:E72,"CL")+COUNTIF(E1:E72,"E12")
and N12 easy enough to counted in the Ns
My problem is I need to count both E12 + N12 as one and to be calculated in the Late shifts
=COUNTIF(E3:E72,"L")+COUNTIF(E3:E72,"LC")+COUNTIF(E3:E72,"FU")
And I'm stuck
Any help is appreciated Thanks
- mtarlerSilver ContributorI'm not sure what the problem is but there are number of tricks you can use to potentially make that more simple. For example you could try:
=SUM(--(E1:E72={"E","E6","I/C","CL","E12"}))
You can also use SUMPRODUCT or maybe ISNUMBER(XMATCH(...))
You can also use the Name Manager to define the array of possibilities and use that name instead of listing it directly so any changes could be done easier. For example make a NAME called "Evenings" and define as
={"E","E6","I/C","CL","E12"}
so then you would have
=SUM(--(E1:E76=Evenings))
hope that gives you some ideas- Manda13130Copper ContributorThanks for the reply. And apologise as I am still struggling. Basics only here for me.
If I add this to my current count formulas it counts as two
+COUNTIF(E3:E72,"E12")+COUNTIF(E3:E72,"N12")
I want them to count as one. E12+N12=1.
I hope that makes sense 😞- mtarlerSilver Contributorhere are a few options maybe
MIN( COUNTIF(E3:E72,"E12")+COUNTIF(E3:E72,"N12") , 1)
MAX( COUNTIF(E3:E72,"E12"), COUNTIF(E3:E72,"N12") )
--OR( COUNTIF(E3:E72,"E12"), COUNTIF(E3:E72,"N12") )
MAX( (E3:E72="E12")+(E3:E72="N12") )