Tip Printed from ExcelTip.com

---

Using Cell Reference with Keyboard signs in the Criteria Range argument when using COUNTIF formula.


Explanation:

When you want to use the greater, less or equal signs ("<",">","=") in the criteria range of the COUNTIF formula you should include the expression within quotation marks.

Example: =COUNTIF(A1:A8,">=4")

Problem:

A problem arises if you want to use a cell reference in place of the number.
It is wrong to include it within quotation marks.

=COUNTIF(A1:A8, ">=B1") since Excel will identify the cell reference as a string

Solution:

The key is to include only the signs (<,>, =) within quotation marks and use the “&” operator to join (CONCATENATE) them with the cell reference:

=COUNTIF(A1:A8, ">="&B1)