Forum Discussion
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 ad...
I'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
=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
- Thanks 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 😞- here 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") )
