Learn how to use the COUNTIF and COUNTIFS functions in Microsoft Excel to count cells based on specific criteria. This step-by-step guide will help you master these useful functions and improve your data analysis skills.
Table of Contents
How to Use =COUNTIF in Excel
The =COUNTIF function in Excel counts cells based that fit a specific argument. Here’s how it looks:
=COUNTIF(Range, Condition)
In the range argument, choose the cells you want to check. If you want to check all the cells in column A, for example, insert A:A into the first position.
In the condition argument, choose the value you want to check for. If it’s a number, you can enter it without any thing else. For example, you can check all cells in column A for the value 5 with the following formula:
=COUNTIF(A:A, 5)
Or you can check for True values, specific text strings, or values in a specific condition. You can also use logical operators in your condition argument. I’ll discuss in more detail below.
Related: How to use COUNTIF in Google Sheets
COUNTIF With a Reference Cell
If you’re building a workbook that automatically updates, you may want to use COUNTIF with a reference cell. For example, you may want to count cells that match the contents of cell B5. In that case, your formula would look like this:
=COUNTIF(A:A, B5)
Note that this formula replaces the “criteria” argument with the cell reference B5. It then checks all cells in the specified range (column A) to see whether they match the value. If they do, they are counted.
This is especially helpful when you want to find duplicates in Excel. The COUNTIF function, using a reference cell, can alert you when there’s more than one occurrence in a specific cell range.
Counting Cells with a TRUE Value
To count cells with a value of TRUE, insert that word into the second argument of the COUNTIF function. Here’s how it looks:
=COUNTIF(A:A,"TRUE")
This example checks every cell in the first column to see whether it contains the exact text, “TRUE”. If the cell contains that and nothing else, it is counted. And the number of cells counted includes those with different cases. This count includes “True” and “true”, for example.
Remember to use double quotes around the “TRUE” value, just like you would for a normal string of text.
Counting Cells Based on a String of Text
To count cells in a workbook based on a text string, you’ll use quote marks around specific words in the COUNTIF function. Here’s an example.
=COUNTIF(A:A,”Mike”)
My video uses this example. It checks the cells in column A to see whether “Mike” is the exact cell content. If it is, the cell is counted.
You can change that text to anything, and you can expand your search by using a wildcard character like star (*) before or after the text. Just remember to insert the wildcard inside your quote marks to show whether the wildcard appears before or after your target text.
Note on Wildcard Characters: I mentioned using a star, but you can also use a question mark (?) to specify a wildcard for individual characters. These are important for partial matching.
So what if you want to use an actual question mark as your string? In that case, use a tilde (~) in your string.
Counting Dates in Excel
You can use the COUNTIF function to count dates after a specific date. Remember, dates in Google Sheets and Excel are stored as numbers. So we can use the Greater Than, Less Than, and Equal-To symbols and their logic in these formulas.
=COUNTIF(A:A,”>=7/1/2024″)
This formula counts cells in the A column when they are greater or equal to July 1st, 2024. There’s also the TODAY function for the current date.
Counting Cells with Multiple Criteria in Excel
If you want to count cells based on multiple criteria, use the COUNTIFS function. Note that it’s similar to the COUNTIF function, but it’s made especially for when you want to count based on more than one attribute.
=COUNTIFS(range, criteria, range_2, criteria_2, . . .)
Say you want to count all cells in column A that contain the number 5 and all those in column B that contain the number 6. In that case, you would use the following:
=COUNTIFS(A:A, 5, B:B, 6)
Note how I included a range for each of the criteria. The same is true if you want to count cells in one range based on multiple criteria. For example, say you want to count only cells in column A that contain either a five or a six. That looks like this:
=COUNTIFS(A:A, 5, A:A, 6)
I just duplicated the target range and changed the criteria for the second range to six. COUNTIFS also allows you to search different ranges for the same criteria. So you could search column A and column C, then use 5 as your search criteria for both. That gives you the quantity of cells in both columns that contain the number 5.
Limitations of The COUNTIF Formula
While COUNTIF is one of the foundational formulas for Excel users, there are limitations. You can’t use it to count cells based on color. At least, you can’t do it without advanced Visual Basic programming.
That’s a recommended skill for power users. You can either take an advanced Excel course or just dive into some of the basics of Visual Basic. Then, you can program macros and other more advanced functions beyond the scope of the simple COUNTIF function.
Related: How to Use the SUMIF Function in Google Sheets
Common Uses and Questions
Here, I’ll share some of the most common questions that come up when using COUNTIF in Microsoft Excel. If I missed anything, please leave a comment. I review every question that comes in and do my best to respond within a few days.
How do you use COUNTIF for blank cells?
Yes, you can use COUNTIF to count blank cells in Excel. Here’s the formula:
=COUNTIF(A:A, "")
The first argument in COUNTIF, if you recall, is the range. Here I’m checking every cell in the first column.
In the second argument, I’m just using two quote marks next to each other. They’re not separated by a space or any character. This tells the formula to count any cell that don’t contain anything.
Can you use COUNTIF for non-blank cells?
=COUNTIF(A:A, "<>")
Note that the first argument is the range. My example formula shows you the whole A column. The second argument is a less than sign and a greater than sign. This tells the formula to count any cell that contains anything.
Can you COUNTIF distinct values?
The COUNTIF Excel function doesn’t count distinct values by itself. However, you can use a different formula to find this. Try using the following:
=SUM(IF(FREQUENCY(MATCH(A1:A10, A1:A10, 0), MATCH(A1:A10, A1:A10, 0))>0, 1))
This formula uses SUM, FREQUENCY, and MATCH functions to check for unique values.
Are criteria case sensitive?
The criteria you include in the Excel COUNTIF function aren’t case-sensitive. If your formula includes “EXAMPLE” your count would include cells with text like “Example” and “example” and even “ExAmPlE”.
It doesn’t matter whether you use uppercase, lowercase, or a mixture of both in your criteria.
Explainer Video: Using COUNTIF in Excel
Video Transcript
Hi there, today I will be explaining how to use the COUNTIF function in Excel. The COUNTIF function is used to count numbers or count specific items within a sheet based on a criteria set which you determine. Let’s say I wanted to count the number of times that Mike has worked in this particular month.
You’ll see the employee name, the number of hours, and the date here. And I want to know a quick way of determining how many times Mike has worked in this particular month. How I will do that is use the COUNTIF function. So I will use equals COUNTIF. Now, the first thing you do is select your range that you’re going to be addressing, which is column A in this case.
So you can select the entire column, or you can just select a range of, of care, of cells that you’re looking at. For this example, I will select the entire column. The criteria, you’re going to want to always put this in quotation marks if it’s text that you’re looking for. You will type in Mike, and it returns the number of times that Mike comes up in this range of cells.
If you were looking to determine how many hours or how many instances of 4 hours is in this particular month, you would just change this column to B, and then you would put 4. There is no need to put quotation marks when you’re trying to count if a number. Okay, so there’s 14 instances of 4 hours in this particular range.
Can you use count if to count cells based on a color? Using count if to count cells based on a color is not possible with the exception of using some advanced visual basic programming, which I will not get into today. However, there is a workaround if you have the number inside of the color. So you’ll see these cells are two and they are blue.
You can use count if that way, count if select a range, and then you will of course put two, just like the previous example. You can also do the same thing if you change this to column B. See if you had the names of the colors beside each cell and you will change it to blue. Don’t forget to add in the quotation marks for text and then it returns two.
Can I use count if to count only cells with a true value? Yes, you can do that exact thing. You would use COUNTIF, the range, of course, is column A, and the criteria is true.
And you will see there are two instances of true there. The last instance of COUNTIF I will show you is if you can use COUNTIF to count cells with values that contain a certain string of text. If I go over to this tab here, I want to count the number of times That the status is absent. So I will use countif, highlight my column B.
Actually, I will select a range this time instead of highlighting the entire column so you can see the different ways. Criteria is absent. Make sure to use quotation marks. You will see it counts as counts the absent as 3. There’s three instances there. Let’s say I wanted it to count this cell though. If you wanted to count the word absent, and it doesn’t have to be an exact match in the cell, you will put wildcards or asterisks after the quotation, but before the word that you’re looking for.
Like so. This way, it puts it to 4 because it sees the word absent 4 times.
That is how you use the COUNTIF function in Excel. For more advice, check out SpreadsheetPoint.com. And don’t forget to like and subscribe.
Conclusion
The COUNTIF function in Excel provides an easy way to count cell values quickly. It’s versatile and works with numbers, strings, and reference cells.
