COUNTIF, Comparisons, and Cell References

While using the COUNTIF/COUNTIFS functions in Excel, I ran across a problem where the formula wouldn’t run. Needless to say, this is very frustrating.

A simple numerical comparison works fine:
=COUNTIF(A1:A10,”<10″)

But a comparison using a cell reference does not work:
=COUNTIF(A1:A10,”

To correct this, put the cell reference outside the quotes and precede it with an ampersand:
=COUNTIF(A1:A10,”<“&B1)

For a long chain of comparions, use COUNTIFS:
=COUNTIFS(A1:A10,”<“&B1, A1:A10,”>=”&C1)

Sources
http://www.excelforum.com/excel-general/666596-why-countif-works-with-a-value-not-a-cell-reference.html
https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

COUNTIF, Comparisons, and Cell References
Scroll to top