Can we nest an IF inside a COUNTIFS in Excel?

Question

I have been working on an attendance sheet and trying to make the monthly reports automatic. I have asked my previous question on the same issue and got the idea to accomplish the task.

But now I have stuck at one place. I have this below formula:

=COUNTIFS(C5:C27,">0", E5:E27,"G", F5:F27,"CAT1")

The value in cell "C" in the above is coming from the below formula (in cell "C")

=IF((COUNTIF(G5:AK5,"p"))>0,1,0)

I had to add this extra column ("C") only to supply input to my fist formula. My question is - "Can we merge the IF function inside the COUNTIFS to get the result in one go and to eliminate the use of an extra column (column C)"?

Answer 1

To perform these cell reference acrobatics you will likely need to switch to an array formula. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. A 'helper' column such as you've used in column C can generally reduce calculation cycles and make a worksheet more 'user friendly'.

A COUNTIFS function requires that the ranges being examined are not only the same size but also the same shape. Looking at G5:AK5 is not the same as looking at E5:E35 even though they contain the same number of cells¹.

In the sample data below, you formula is in A1 and uses the 'helper column' C. My array formula is in A2 and does not consider column C ahough it incorporated the logic.

        

The array formula in A2 is:

=SUM(IF(E5:E27 = "G", IF(F5:F27 = "CAT1", SIGN(COUNTIFS(OFFSET($G$5, ROW($1:$23)-1, 1, 1, 31), $I2)))))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

¹_{Some functions not only accept but welcome cell ranges that are the same number of calls but transposed. Offsetting or staggering the ranges is also an option if the cell ranges are the same size. In difficult cases the TRANSPOSE function can be helpful.}