< Go Back

Excel COUNTIF Function

Excel COUNTIF Function

The formula for the Excel COUNTIF Function

=COUNTIF (range, criteria)

Explanation

The Excel COUNTIF function is designed to counts cells within a range that meets particular criteria. With Excel COUNTIF function, you can count cells with dates, numbers, and text that match particular criteria. The Excel COUNTIF function also supports logical operators such as >, <, <>, =. Does it support wildcards such as:* and? where partial matching is required.

Purpose of the Excel COUNTIF Function

The Excel COUNTIF function count all the cells that match a certain criteria

Return Value for the Excel COUNTIF Function 

The Excel COUNTIF function gives a number that is a representation of cells counted.

Logical Arguments of the Excel COUNTIF Function

The Excel COUNTIF function uses only two logical arguments which are the range and the criteria to be met

range – This is the range of cells to be counted.

criteria – This is the reason that controls why a cell has to be counted.

Example 1

From the example below

=COUNTIF(B7:B13,"Orange")

count cells that contain the fruit ‘orange’

Figure 1: Example of how to use the Excel COUNTIF Function to count cell that contains “Orange”

Example 2:

The formula used here is:

=COUNTIF(D7:D13,“<140”)

This formula counts cells with Amount below $140

Figure 2: Example on how to use the Excel COUNTIF Function to count cells containing an amount less than $140

Example 3:

The formula

=COUNTIF(C7:C13,">9")

counts cells with the number greater than 9

Figure 3: Example of how to use the Excel COUNTIF Function to count cells containing Number value above 9

Usage Notes for the Excel COUNTIF Function

  • The Excel COUNTIF function will only count the number of cells within a range that match particular criteria supplied
  • For non-numeric criteria, they should be enclosed in double quotes while numeric criteria do not these quotes enclosure
  • Wildcard characters such as ‘?’ and ‘*’ can also be used as a criterion. In this case, a question mark is used to match any single character while an asterisk is used to match any sequence of characters within cells.
  • To find a question mark or asterisk with a literal meaning, use a tilde ‘~’ in before the question mark or asterisk (example ~? or ~*).
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar