Forum Discussion

Manda13130's avatar
Manda13130
Copper Contributor
Mar 27, 2023

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

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    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
    • Manda13130's avatar
      Manda13130
      Copper Contributor
      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 😞
      • mtarler's avatar
        mtarler
        Silver Contributor
        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") )

Resources