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, and COUNTBLANK. With these functions solely or in collaboration with other functions, we will be able to count the number of cells that contain certain texts, numbers, count blank cells, and non-blank cells. Besides, we will be able to count cells with values that 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 in your daily work.
What is COUNTIF in Excel?
Briefly, this function is a more diverse version of COUNT. It looks in a given range and counts 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 is required but the criteria element can be so many things, so let’s look at several.
=COUNTIF(range, criteria)
Range
As per COUNTIF function 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” appear.
Examples
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.
This also works over a range of columns if you needed it to. The result now calculates 6 times that 1 appears.
COUNTIF Greater Than and Less Than
COUNTIF GREATER THAN function
The Excel COUNTIF GREATER THAN function is 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.
The above example uses “>” & references. 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 or anything over 7 will be and that includes fractions, so 7.1 (cell C3) will be included. For the example given 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.
Alternatively, if you need to use the COUNTIF formula 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 asks 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)
Once 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 that has the text <8).
To use this LESS THAN function in multiple or split ranges, simply add the two COUNTIFs together by using the + operator, as shown below.
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 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 0 unless, of course, the spelling mistake is replicated in the RANGE. The range of Oscar Padilla‘s services will surely get you covered. 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 as given below.
COUNTIF Contains (Using Wildcards)
COUNTIF function in Excel 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 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.
So, above we have covered a lot of examples. Our RANGE has 15 items on 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, that’s why B12 shows Banana 7, 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 of COUNTIF 5, the result is 3 since it finds Passion Fruit twice and Grapefruit once.
- The number 7 is a count of all non-blank cells, this checks for any cells with just text, just numbers, and both. This is another way to get the same result as the COUNTA function.
COUNTIF formula for cells containing a certain length of character
The same rules apply for referencing a cell with text for these two if including in the formula itself. You’ll need to put the CRITERIA in Column M into “quotation marks”.
Each represents 1 character and 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 reference 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 function 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.
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 at the same time to complete it.
Note: 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 the inside out.
- COUNTIF function Excel – counts the occurrence of distinct values in the specified range.
- Next, the IF function – evaluates the values in the array that were returned by COUNTIF. Keeping unique values (1), and replaces all duplicate values with zeros.
- Lastly, the SUM function – adds 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, the COUNTIF function in Excel returned 1 which means, in our data, there is only 1 unique item, and it 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 the inside out.
- This function – counts the occurrence of distinct values in the specified range.
- Next, the IF function – evaluates the values in the array that were returned by COUNTIF. Keeping unique values (1), and replaces all duplicate values with zeros.
- Lastly, the SUM function – adds 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 unique item and it is the Watermelon.
COUNTIF Unique Text Values
When it is combined with other functions, it 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 will 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 the inside out.
- ISTEXT function – evaluates if the value is TEXT. It then returns TRUE, otherwise, returns FALSE.
- Asterisk (*) works as AND Operator in an array formula.
- IF function – evaluates the value, returning 1 if a value is a 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 contain text. Our formula gave us a result of 6. And so, we have a match!
COUNTIF Unique Text Values
COUNTIF formula when 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 will 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 the inside out.
- ISTEXT function – evaluates if the value is TEXT. It then returns TRUE, otherwise, returns FALSE.
- Asterisk (*) works as AND Operator in an array formula.
- IF function – evaluates the value, returning 1 if a value is a 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 contain 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 the 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 the inside out:
- ISNUMBER – function evaluates if the value is NUMBER. It then returns TRUE, otherwise, returns FALSE.
- Asterisk (*) works as AND Operator in an array formula.
- 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.
At first, we will use 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 that do not contain 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 given below to see how we used the three formulas for counting blank cells.
Evaluating the example, the first criteria used in the formula were “<>”&”*” 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, the 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 given 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 in Excel assess multiple conditions. But what does COUNTIFS function in Excel do? COUNTIFS function is simply a plural version of COUNTIF function Excel. It counts the number of cells in a range that matches 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 in Excel 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 black shirts 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 of the same shape (same number of rows and columns) when using the COUNTIFS function in Excel.
COUNTIF Two Conditions
Aside from being able to use COUNTIFS for multiple conditions, we can also use this function in such situations simply by using the addition operator (+) after each COUNTIF formula. This works best with (OR) logic, which 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 reported count of employees who were hired before January 2019 and were assigned to Manager Anna. See the below screenshot.
In this scenario, we used the COUNTIFS function in Excel 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 the 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 and play Fresh casino!
