The Excel COUNTIF Function

Introduction to Conditional Counting

We all know that Excel has a variety of built-in functions. But did you know that the most used functions in Excel are the functions that count and sum? So today, I am happy to discuss and explain these functions under the statistical category specifically those used for conditional counting – COUNTIF, COUNTIFS, COUNTBLANK. With these COUNTIF functions solely or in collaboration with other functions, we will be able to count a number of cells that contain certain texts, numbers, count blank cells, non-blank cells and whose values meet a certain COUNTIFS condition specified in the COUNTIFS formula. So by the end of this article, I hope you will learn how and where to use these conditional counting functions on your daily work.

COUNTIF *COUNTIF *COUNTIF *COUNTIF *COUNTIF *COUNTIF *COUNTIF

What does COUNTIF do?

In brief summary, this function is a more diverse version of COUNT.  It looks in a given range and counts up the number of times a cell meets a given condition.  This can be a number of different conditions, from matching text, numbers, cells containing text, matching part text and more.

COUNTIF Syntax explained

A small function in COUNTIF in terms of arguments required, but the criteria element can be so many things so let’s look at several.

=COUNTIF(range, criteria)

Range

As per COUNTIF Excel, it defines this as “the range of cells from which you wish to count nonblank cells”.   Strictly speaking, you can use this function to count blank cells if you want to, alternatively, there is a COUNTBLANK function if you wanted to count blank cells in a range

Criteria

Row_num

As per COUNTIFS Excel, it defines this as “the condition in the form of a number, expression, or text that defines which cells will be counted.”This is where the magic happens, we’ll look at the example below but you can ask to find the number of times “apples” appears.

Examples

Before we look at several examples let’s look at one basic COUNTIFS version.

Below we select our array B3:B7 and ask it to COUNT the number of times that 1 appears.

function

This also works over a range of columns if you needed it to.  The result now calculates a total of 6 times that 1 appears.

range

Greater Than and Less Than

GREATER THAN function

The Excel COUNTIF GREATER THAN function is a basically using this function and the ‘>’ symbol (GREATER THAN) as your criteria combined with a number value. This number represents the boundary above which you want to count. This number can be entered directly into the formula or referred to on the worksheet.

Examples

Before we look at several examples let’s look at one basic version.

Below we select our array B3:B12 and ask it to COUNT the number of times that it finds a number GREATER THAN the value found in cell E2, in this case it’s 4.

countif

The above example uses “>”&reference.  We need to put > into “quotation” marks because it’s text.  We also need to use the & symbol to join the text.

This                                        =COUNTIF(B3:B12,”>”&E2)          (where E2 has the value 4)

Is the same as this           =COUNTIF(B3:B12,”>”&4)

As well as this                    =COUNTIF(B3:B12,E2)                    (where E2 has the text >4)

And even this                    =COUNTIF(B3:B12,criteria1)        (where E2 and is a named range called

criteria1 and has the text >4)

As per function this too works over a range of columns if you needed it to.  The result now calculates a total of 9 times where the data is GREATER THAN 7.

Note, that 7 will NOT be counted into the total, anything over 7 will be and that includes fractions, so 7.1 (cell C3) will be included.  For the example below its counting these (top to bottom, column B then C in that order):

  • 9, 8, 10, 7.1, 9, 8, 77, 11, and 7.11. That gives us the 9 results which matches below.

countif

Alternatively, if you need to use COUNTIF over a split range you can work it as per below by simply ADDING two COUNTIFs together.  If you need to add more, insert another + symbol and repeat for the new range and so on.

countif

LESS THAN function

This LESS THAN function can be used to count the number of cells that contain values LESS THAN a specified number. This number represents the boundary below which you want to count. This number can be entered directly into the formula or referred to on the worksheet.

Examples

Our range B3:B12 contains our data. Criteria “<”&E2 ask it to COUNT the number of times that it finds a number LESS THAN the value found in cell E2, in this case it’s 8.

Giving us a result of (1, 1, 3, 5, 6, 4.1, and 3.9)

countif

Again, the above example uses “<”&reference.  We need to put < into “quotation” marks because it’s text.  We also need to use the & symbol to join the text.

This                                        =COUNTIF(B3:B12,”<“&E2)          (where E2 has the value 8)

Is the same as this           =COUNTIF(B3:B12,”<“&8)

As well as this                    =COUNTIF(B3:B12,E2)                    (where E2 has the text <8)

And even this                    =COUNTIF(B3:B12,criteria1)        (where E2 and is a named range called

criteria1 and has the text <8)

To use this LESS THAN in multiple or split ranges, simply add the two COUNTIFs together by using + operator, as shown below.

countif

Once more, if you need to use this over a split range you can work it as per below by simply ADDING two COUNTIFs together.  If you need to add more, insert another + symbol and repeat for the new range and so on.

countif

COUNTIF contains text

When you want to count the number of cells that contain a certain text, use the COUNTIF function, type the text enclosed in “ “ as the criteria or simply refer to a cell in the worksheet containing the criteria as shown on the example below.

The text search is not case sensitive, i.e. searching for Apple will yield the same result as searching for APPLE, or ApplE etc.  As with any search criteria it must match exactly, so no spelling mistakes and watch for rogue spaces at the end of any text string.

Above we can count the number of cells in our RANGE (column B) when it contains the CRITERIA as shown in column D.  D4 contains a deliberate error in spelling, this will return a 0 unless of course the spelling mistake is replicated in the RANGE.  Lemon is also included in the CRITERIA list, this doesn’t appear in the RANGE.

You can either refer to a cell with text or incorporate the text into the formula like below.

COUNTIF contains (Using Wildcards)

COUNTIF for cells containing part of a word

There are a number of ways to search a range for single, multiple letters, full words, any text and also any text/numbers.  Below we can look at all of these by using the wildcard *, placing the * indicates there should be some text in place of it.  Where you place it determines where the missing text should be.

So above we cover a lot of example.  Our RANGE has 15 items in its list.

  • COUNTIF number 1 on row 3 looks for cells where it contains an ‘e’ anywhere in the text, could be the start, middle or end.
  • COUNTIF 2 does the same but ONLY those cells where it begins with an ‘a’. Here we have 3 occurrences of the word Apple.
  • COUNTIF 3 does this same as 1 but looks for ‘ss’ together, this picks up the ‘ss’ in Passion Fruit.
  • COUNTIF 4 is an asterisk on its own, this will search the range for any cells that contain text, it will also count a cell if it contains text AND a number, hence why B12 shows Banana7 yet the result of COUNTIF 4 is 14 (it’s omitted B11 since it’s number ONLY). There are 15 items in the range.
  • COUNTIF 5 looks for a full word at the start of the cell followed by any text. This shows zero since none of the text starts with the word fruit.
  • COUNTIF 6 is a test of the opposite to COUNTIF 5, this result is 3 since it finds Passion Fruit twice and Grapefruit once.
  • COUNTIF 7 is a count of all non-blank cells, this check for any cells with just text, just numbers and both. This is another way to get the same result as the COUNTA function.

COUNTIF for cells containing a certain length of character

Same rules apply for referencing a cell with text for these too, if including in the formula itself, you’ll need to put the CRITERIA in Column M into “quotation marks”.

Each? represents 1 character, note this works for TEXT ONLY, see N6 which looks for 2 letters, if it worked with numbers then the result should be 1 as 52 appears in B11.

  • 5, 7, 2 and 10 letters are represented by their equal quantity in ? marks.
  • You can also add a letter into the mix as shown on row 4, this picks up the 3 x Apple in the RANGE

If your result is zero, check carefully for any typos, rogue spaces, missing question marks or that your text (if entering directly into the function) is inserted between the quotation marks etc.

Date Range

Counting cells with dates that are greater than, less than or equal to a specified date can also be performed using COUNTIF function.

Again, there are two ways to do this, one is to type the date criteria directly into the formula or by referencing to a cell in the worksheet.

Example:

=COUNTIF(range,”>1/1/2019”) – count the dates greater than January 1, 2019

Or

=COUNTIF(range,”>”&B1) – where B1 contains the value January 1, 2019

Here are other sample formulas to help you better understand how to use COUNTIF with dates.

Aside from this, you can also combine specific Excel Date and Time functions with COUNTIF such as TODAY()  to count cells based on the current date.

Example:

=COUNTIF(range,TODAY()) – counts dates that are equal to the current date

Below are more ways to use COUNTIF and TODAY functions together.

COUNTIF Unique

Large datasets might be tough to handle, oftentimes you would need to know how many unique items there are. Here are examples to demonstrate how to use COUNTIF function in different scenarios.

COUNTIF unique values in a column

To count unique values in a column, use the SUM function combined with IF and COUNTIF. And since this is an array formula, you need to press CTRL+ SHIFT+ENTER to complete it.

Note that manually typing the curly braces won’t work, pressing CTRL+ SHIFT+ENTER will automatically enclose the formula in curly braces {}. This is how it will look:

{=SUM(IF(COUNTIF(range, range)=1,1,0))}

How does the formula work?

We’ll discuss the three functions used in the formula from inside out.

  1. COUNTIF function – COUNTIFS counts the occurrence of distinct value in the specified range.
  2. Next, IF function – evaluates the values in the array that were returned by COUNTIF. Keeping all unique values (1), and replaces all duplicate values with zeros.
  3. Lastly, SUM function – adds up all the values in the array returned by IF and gives us the total count of unique values.

Here is an example of how the formula was implemented.

Evaluating the data and the result, COUNTIF function returned 1 which means, in our data, there is only 1 item that is unique, that is the Watermelon.

COUNTIF unique text values

COUNTIF, combined with other functions can count unique values of different sorts. For instance, counting only unique text values in a range containing both numbers and text values by using functions:  SUM, IF, ISTEXT and of course, COUNTIF.

And since this is an COUNTIFS array formula, you need to press CTRL+ SHIFT+ENTER to complete it.

Note that manually typing the curly braces won’t work, pressing CTRL+ SHIFT+ENTER will automatically enclose the formula in curly braces {}. This is how it will look:

{=SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0))}

How does the formula work?

Let’s discuss the formula from inside out.

  1. ISTEXT function – COUNTIFS  evaluates if value is TEXT. It then returns TRUE, otherwise, returns FALSE.
  2. Asterisk (*) works as AND Operator in an array formula.
  3. IF function -COUNTIFS evaluates the value, returning 1 if a value is text and unique, else, returns 0.

Below is an example of how the formula was implemented.

Let’s evaluate the data and result. Our data shows we have 6 unique entries that contains text. Our formula gave us a result of 6. And so we have a match!

Unique numeric values

Counting unique numeric values in a range is the same as counting unique text values. We simply need to replace the ISTEXT function with ISNUMBER function.

Again, this is an array formula, so you need to press CTRL+ SHIFT+ENTER to complete it.

Note that manually typing the curly braces won’t work in COUNTIF, pressing CTRL+ SHIFT+ENTER will automatically enclose the formula in curly braces {}. This is how it will look:

{=SUM(IF(ISNUMBER(range)*COUNTIF(range,range)=1,1,0))}

How does the formula work?

Again, let’s discuss the formula from inside out:

  1. ISNUMBER – COUNTIFS  function evaluates if the value is NUMBER. It then returns TRUE, otherwise, returns FALSE.
  2. Asterisk (*) works as AND Operator in an array formula.
  3. IF function – COUNTIFS  evaluates the value, returning 1 if the value is number and unique, else, returns 0.

Here is an example of how the formula was implemented in COUNTIF.

Using our previous data in unique text values, but now let’s count the entries that contain unique number values. Manually counting we can see that we have 8 unique number values. Our formula gave us a result of 8. Again, we have a match.

COUNTIF blank

There are 2 ways to count blank cells in a COUNTIFS certain range. One is to use a formula with a wildcard character, an asterisk (*) for text values and the other is to use (“ “) as a criterion to count all empty cells.

=COUNTIF(range,”<>”&”*”)  means to count cells not containing any text. The downside is that this formula considers numbers and dates as blanks. Leaving us with the second formula, which is the universal COUNTIF formula for blanks =COUNTIF(range,” “). This formula handles numbers, text values and dates correctly.

Did you know that Excel also has another COUNTIFS function for counting blank cells? =COUNTBLANK(range) yields exactly the same results as the COUNTIF(range,” “) formula.

You can refer to the screenshot below to see how we used the three formulas for counting blank cells.

Evaluating the example, the first criteria used in the formula was “<>”&”*” this formula counts numbers as blank cells, and only considers texts as non-blanks. The second formula uses “ “ as criteria. By manual count, we can see that there are 3 blank cells. The third formula uses the COUNTBLANK function, and also gives us a result of 3 which matches our manual validation.

If you need to count non-blank cells in a set of cells or range, COUNTA function works perfectly. It returns the count of all non-blank values including numbers, dates, text values, logical values and errors. The syntax for COUNTA is:

=COUNTA(value1, value2, …)

Refer to the screenshot below for a visual example.

Evaluating our data by manual count, we can see that there are 13 non-blank cells for range A2:A17. Then again, it matches with the result returned by our formula.

COUNTIF multiple criteria

There are several approaches on how COUNTIF and COUNTIFS functions assesses multiple conditions. But what does COUNTIFS function do? COUNTIFS function is simply a plural version of COUNTIF function. COUNTIFS counts the number of cells in a range that match a set of multiple criteria. The syntax is:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)

Syntax Arguments:

Criteria_range1 – the range of cells you want to evaluate against criteria1

Criteria1 – criteria to determine which cells to count in criteria_range1

Criteria_range2 – the range of cells you want to evaluate against criteria2

Criteria2 – criteria to determine  which cells to count in criteria_range2

You can use several criteria_range and criteria pairs in a single COUNTIFS function just as long as each one must be the same shape, and by shape, we are talking about the number of columns and rows.

A sample scenario is to count all small shirts that are black and all small shirts that are white as shown below. On our next example, notice that if criteria_range2 has a different size (row count), it will return #VALUE! error. So as discussed previously, it is very important that each range must be the same shape (same number of rows and columns) when using COUNTIFS function.

COUNTIF two conditions

Aside from being able to use COUNTIFS for multiple conditions, we can also use regular COUNTIF function in such situations simply by using the addition operator (+) after each COUNTIF formula. This best works with (OR) logic, that means at least one of the specified conditions returns TRUE.

Let’s try an example wherein we must count order statuses that were either returned or refunded.

By looking at our example, we can validate that there are a total of 4 refunded and returned statuses in our list. Same as the result given by our formula.

We used the syntax:

=COUNTIF($Q$2:$Q$12,”Refunded”)+COUNTIF($Q$2:$Q$12,”Returned”)

COUNTIFS Multiple criteria different columns

After learning how to count values with COUNTIFS multiple conditions in the same range, we will be moving forward to a more complicated situation. Now, the scenario is: You must create a reported count of employees who was hired before January 2019 and were assigned to Manager Anna. See below screenshot.

In this scenario, we used the COUNTIFS function mainly because the COUNTIFS function works best on data with multiple criteria in different columns. COUNTIFS counts values that meet all of the specified criteria in the formula. Keep in mind that additional range must have the same number of rows and columns as the first range (criteria_range1). Otherwise, COUNTIFS will return #VALUE! error.

Conclusion

I hope you have found this article useful. Remember that a lot of practice is the key to improve your Excel skills. Also, continue to learn and try combining these counting functions along with other functions to hone your skills and be able to move beyond the basics in Excel.

Best of luck!