formula help

[Manda13130]

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

[#2]

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

[#3]

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 :(

[#4]

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") )