COUNTIF

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 (the best I could!) these functions under the statistical category specifically those used for conditional counting – COUNTIF, COUNTIFS, COUNTBLANK. With these functions solely or in collaboration with other functions, we will be able to count number of cells that contain certain texts, numbers, count blank cells, non-blank cells and whose values meet a certain condition specified in the 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.

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 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

Get Our Guide To The 27 Best Excel Formulas When You Subscribe To Our Awesome Emails 

*We are GDPR compliant and take your privacy very seriously. No spam. Just great content

Before we look at several examples let’s look at one basic 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.  Result now calculates a total of 6 times that 1 appears.

range

COUNTIF Greater Than and Less Than

COUNTIF 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

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 7  (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 this 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.

  • The number 1 on row 3 looks for cells where it contains an ‘e’ anywhere in the text, could be the start, middle or end.
  • The number 2 does the same but ONLY those cells where it begins with an ‘a’. Here we have 3 occurrences of the word Apple.
  • The number 3 does this same as 1 but looks for ‘ss’ together, this picks up the ‘ss’ in Passion Fruit.
  • The number 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.
  • The number 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.
  • The number 6 is a test of the opposite to COUNTIF 5, this result is 3 since it finds Passion Fruit twice and Grapefruit once.
  • The number 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.

COUNTIF Date Range

Counting cells with dates that are greater than, less than or equal to a specified date can also be performed using this 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 this funtion 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 this function and TODAY functions together.

Stop Wasting Time With The WRONG Excel Formulas!!

Our Free Excel Formulas Book will teach you the 27 BEST Formulas that you need for your work. Subscribe to our Excel-lent emails and we’ll send you the book right away!

*We are GDPR compliant and take your privacy very seriously. No spam. Just great content

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 this 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 – 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 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. This function – 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, this function returned 1 which means, in our data, there is only 1 item that is unique, that is the Watermelon.

COUNTIF unique text values

It 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 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 – 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 – 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!

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 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 – 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 – 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!

COUNTIF 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, 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 – 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 – 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.

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 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 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 with COUNTIFS

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. It 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 this 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”)

COUNTIF multiple criteria different columns

After learning how to count values with multiple conditions in the same range, we will be moving forward to a more complicated situation. Now, the scenario is: You must create a report count of employees who were 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. It 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, it 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!

Stop Wasting Time With The WRONG Excel Formulas!!

Our Free Excel Formulas Book will teach you the 27 BEST Formulas that you need for your work. Subscribe to our Excel-lent emails and we’ll send you the book right away!

*We are GDPR compliant and take your privacy very seriously. No spam. Just great content

Contents

COUNTIF