0

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

Community
  • 1
  • 1
user3405976
  • 51
  • 1
  • 2
  • 8
  • possible duplicate of [Using COUNTIF in Excel to count number of “Greater than” values in an array of values](http://superuser.com/q/858997/52365) – GSerg Aug 16 '15 at 17:55
  • 1
    @GSerg - I have gone through the question, but I don't think both questions are similar. I also had a search for the answer before asking the question, but did not get any. – user3405976 Aug 16 '15 at 18:03
  • What happened when you tried this? – chris neilsen Aug 16 '15 at 19:45
  • @GSerg - Not even close to a duplicate. –  Aug 17 '15 at 00:09

1 Answers1

0

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.

        GSerg's comment is wrong.

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.

  • Thank you for your response. But I'm very new to Excel thing. I tried your formula, but it is not giving the expected output and I am not able to edit it to give the actual cell references. Can you pleas elaborate the formula especially the 2nd part (SIGN... onward)? I can see that you refer cell G5, the starting point of "P" but where you supplied the end point (AK5 in my case) and then the other arguments supplied. – user3405976 Aug 18 '15 at 10:56
  • I got it finally. Thanks – user3405976 Aug 23 '15 at 00:28