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/