Search code examples
arraysexcelcountifsumifs

CountIF in Excel with a Variable Range for the Criteria

I want to do a variation of CountIf() that tests the criteria against, not just one cell, but across a range of cells that corresponds to the range of cells being tested. Can I do this without creating an additional column?

For example, my spreadsheet looks something like this:

 X     Y
 4     3
 8     9
 6     6
 4     7
 2     1

I would like to count all the rows for which the value in X is greater than or equal to the value in Y. So, here the answer would be 3.

I'm aware of countif(X1:X5,">="&Y1), but countif(X1:X5,">="&Y1:Y5) returns a value of "0". I could add an additional column, use an if() to test my criteria, and then sum that up; but, I have a lot of columns I'd like to do this for and would prefer not to clutter-up my spreadsheet.

Any suggestions on how I can do this in one function? Thanks for your help!

Solution

  • Use SUMPRODUCT instead:

    =SUMPRODUCT(--(X1:X5>=Y1:Y5))

    It iterates both ranges together and counts the True