Many of Excel’s built-in functions are designed to make it easier to find and organize data within your spreadsheet. The COUNTIF function is no exception. If you find yourself wanting to find data that meets a certain criterion, and you know where in your spreadsheet to look, then COUNTIF can be extremely useful. As an added bonus, it is pretty easy to use, especially when compared with many of Excel’s more complex functions.
But if you’ve never used the COUNTIF function in Excel before, we understand if you have a few questions. For instance, what does the COUNTIF function mean? How does it work? What formula do you need to use in Excel to find specific types of data? Can you use COUNTIF with multiple criteria? Finally, are there alternatives to COUNTIF?
In today’s guide, we will answer all of these questions and more. And remember, if you need additional help with Excel, you can always reach out to the trained IT experts at Geeker. Now let’s get started!
What Does COUNTIF Mean & What Does It Do?
While there’s no textbook meaning of COUNTIF, its name pretty accurately describes its function. Essentially, it is a type of search function in which you ask Excel to only “count” pieces of data “if” they meet a specific criterion. This way, you don’t have to manually look through your spreadsheet to find matching cells or strings of characters; you can just input the correct formula and have Excel do the hard work for you!
For example, let’s say you have a data set that looks something like this:
| A | B | C | D | E | F | |
| First Name | Last Name | Role | Location | Salary | Starting Date | |
| 1 | Bill | Russell | Supervisor | Building A | $100,000 | 10/12/2020 |
| 2 | Margaret | Hanson | Manager | Building A | $80,000 | 01/06/2017 |
| 3 | Anne | Jones | Manager | Building A | $80,000 | 05/20/2021 |
| 4 | Max | Patel | Assistant | Building B | $50,000 | 06/15/2016 |
| 5 | Ross | Malone | Clerk | Building B | $42,000 | 11/21/2022 |
Naturally, this is a very small dataset, so it wouldn’t be that hard to find matching pieces of data manually. However, if you were to expand this kind of dataset to include a hundred different names, roles, locations, and salaries, you would definitely want to use COUNTIF to make your search easier.
So, using the above example, let’s say you want to find how many managers there are. By looking at all of the data between A1 and F5, it would come back with two results (C2 and C3). Alternatively, you might want to find out how many employees work in Building A. Using the same parameters, you would get back three results (D1, D2, and D3). Lastly, you might want to see how many employees have a salary at or above $45,000. Once again using the same parameters, you would get back four results (E1, E2, E3, and E4).
How To Use COUNTIF In Excel
In the previous section, we covered the basics of what you can do with COUNTIF. Finding matching strings of text is the most common way to use it, but it’s certainly not the only way. Here are a few different ways to use COUNTIF in Excel, including the appropriate formulas needed:
CountIf Cell Contains Text
There are a few different ways to use the COUNTIF function with text. If you simply want to find cells within a given range that contain any text at all, you would first identify the range and then tell Excel to look for text. Assuming that your range is A1 to F5, your formula would look like this:
=COUNTIF(A1:F5,”*”)
If you want to look for specific text values, you can also do it with COUNTIF. Using the example from the previous section, you can find the term “manager” with this formula:
=COUNTIF(A1:F5,”Manager”)
Or if you want to use COUNTIF by date, you could use a formula like this:
=COUNTIF(A1:F5,”10/12/202”)
*Note: It’s important to put quotation marks around the criterion you use, otherwise Excel might not return any results.
CountIf Blank Cells
Finding blank cells is pretty easy without COUNTIF, but when you have a large dataset, you probably won’t feel like sifting through everything to find the blank cells. So, if you want to use the COUNTIF function for blank cells, you simply need to identify your range and then add an equals sign as your criterion, like this:
=COUNTIF(A1:F5,”=”)
CountIf Not Blank
Once again, separating cells with data from blank cells is usually easy to do with the naked eye. But if you have a lot of data in your spreadsheets, you may prefer to use the COUNTIF function to make it easier on yourself and to save time. To find all cells that are not blank within your chosen range, you can use a formula like this one:
=COUNTIF(A1:F5,”<>”)
CountIf Colored Cells
You may have cells with different background colors that provide a better visual experience or separate different kinds of data. If you want to find specific colors, you can use the COUNTIF function to do it. First, you’ll need to click on a cell with the color you want to find. Then, you’ll need to input the following formula:
=COUNTIF(A1:F5,”GetColor”)
CountIf Distinct Values
Finding distinct or unique values with the COUNTIF value gets a little more complicated. This is because you can’t just use COUNTIF; you’ll actually need to use a combination of the SUM, IF, and COUNTIF functions. You’ll actually need to enter your desired range twice, followed by specific inputs to denote unique values. Here is what a sample formula would look like:
=SUM(IF(COUNTIF(A1:F5, A1:F5)=1,1,0))
How To COUNTIF With Multiple Criteria
The COUNTIF formula changes when you have multiple criteria. Fortunately, the change makes sense and is easy to implement. You simply have to replace the standard formula with “COUNTIFS.” The added “s” tells Excel that you want to search using multiple criteria at once. It’s important to note that you can also search for criteria in multiple ranges. Consequently, COUNTIFS formulas can actually get really complex, depending on what you’re searching for.
Here’s a sample formula using two ranges and two criteria:
=COUNTIFS(A1:C5,”Manager”,D1:F5,”05/20/2021”)
And there you have it! Now you know how to use the COUNTIF and COUNTIFS functions. That said, you may run into issues when you first get started. Some of the most common issues include:
- Long Text Strings – The COUNTIF function only works with text strings that are no more than 255 characters long. If you try to search for text that is longer than 255 characters, you will likely get an error message.
- No Value Returned – If no value is returned, it usually means that your formula is not formatted correctly. You may have forgotten to put the quotation marks around the criterion.
- Closed Workbook – If you try to use COUNTIF for values in a closed workbook, you’ll get an error message. To make it work, you’ll need to make sure that the workbook is open before you proceed.
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!