Count nonblank cells except with certain criteria

[rcp]

Hi,

I'm trying to create a new rota for my workplace [Google Sheets – image below-

Column D needs to be a total number of nonblank cells, but only if they're not any of SICK, HOL, SAB, OFF, MAT, PAT, R/O. (So in that image, it would just be 4).

I'm currently using

=COUNTA(G16:AO16)-SUM(COUNTIFS(G16:AO16,{"SICK","HOL","SAB","OFF","MAT","PAT","R/O"}))

, which is working in not counting the SICK, but not anything else.

(I can't just ask it to count 12; 12.30; 1.30, as there will be far more criteria that could be added).

Thank you so much for any help you can give here

 

[mrbenhop]

=COUNTA(G16:AO16)-sum(countif(G16:AO16,"SICK")+countif(G16:AO16,"HOL")+countif(G16:AO16,"SAB")+countif(G16:AO16,"OFF")+countif(G16:AO16,"MAT")+countif(G16:AO16,"PAT")+countif(G16:AO16,"R/O"))

Should work

 

[rcp]

=COUNTA(G16:AO16)-sum(countif(G16:AO16,"SICK")+countif(G16:AO16,"HOL")+countif(G16:AO16,"SAB")+countif(G16:AO16,"OFF")+countif(G16:AO16,"MAT")+countif(G16:AO16,"PAT")+countif(G16:AO16,"R/O"))

Should work

That's brilliant, thank you!

 

[smozgur]

@rcp - if your question is answered, then that would be great if you could mark the solution post to help future readers.

Also, my approach to the question would be a shorter formula using the ARRAYFORMULA function as shown below.

By using the ARRAYFORMULA function in Google Sheets:

=COUNTA(G16:AO16)-ARRAYFORMULA(SUM(COUNTIF(G16:AO16,{"SICK","HOL","SAB","OFF","MAT","PAT","R/O"})) )

Or, by using SUMPRODUCT if you are trying to count numerics only:

=SUMPRODUCT(--ISNUMBER(G16:AO16))

Note that -- is necessary if you'd like to make the formula compatible with Excel as well.