You may be looking to count the number of occurrences in Excel to analyze trends, patterns, and outliers in your data.
Excel has several functions to assist you in counting occurrences within the spreadsheet. Among such functions, COUNTIF and COUNTIFS functions count occurrences based on specific conditions.
Similarly, Excel has three dedicated functions such as COUNTBLANK, COUNTA, and COUNT to count blanks, non-blanks, and numbers, respectively.
In this article, we will dive into all the ways you could count the number of occurrences in Excel.
Count Occurrence of a Single Value
| Function Used | Data Type | Syntax |
| COUNTIF | STATISTICAL | =COUTIF(range, criteria) |
Say, you’re analyzing the sales sheet of a company that distributes home appliances. Your manager asks you to list the total units sold for each product. How would you automate this task?
The answer is the COUNTIF function.
Let’s use COUNTIF to count how many stoves this company sold in the month of July. Similarly, let’s also count the number of appliances that cost over $150.
In cell G6, I entered the following formula to count the number of Stoves:
=COUNTIF(B5:B20,"Stove")
In cell G10, here’s the formula I entered to count the number of appliances that cost over $150.
=COUNTIF(D5:D20,">150")
Count Occurrence of Multiple Values with Different Criteria
| Function Used | Data Type | Syntax |
| COUNTIFS | STATISTICAL | =COUNTIFS(criteria_range1,criteria1,...) |
When you have multiple criteria to make counts, using COUNTIF will be a bit difficult. Instead, use the COUNTIFS function to set multiple criteria for making your count.
Let’s put this function to use. In this sheet, I have an attendance sheet for a workshop with 3 different sessions.
We’ve been assigned to count the number of employees with perfect attendance. Now, we need to consider all three sessions when making the attendance count.
In cell F6, here’s the formula I used to make this count:
=COUNTIFS(B5:B25,"Present",C5:C25,"Present",D5:D25,"Present")
Use IF to Count Occurrence Based on Conditions
| Function Used | Data Type | Syntax |
| IF | BOOLEAN | =IF(logical_text,[value_if_true],[value_if_false]) |
| COUNTA | STATISTICAL | =COUNTIF(value1,[value2…]) |
You can count occurrences based on conditions set using the COUNTA and IF functions.
The COUNTA function in Excel is used to count non-blank cells. This includes text, numeric, alphanumeric, and even hidden characters.
If you’ve been using Excel for a while, you must already be familiar with the IF function. It returns TRUE or FALSE based on a set criteria. However, you can also customize the returned value if it’s true or false.
We can nest the COUNTA function inside the IF function to count values based on criteria.
Take a look at this spreadsheet
We need to count the days an employee was present in the 7-day workshop.
Using the IF function, we will check if the value is equal to “Employed.” If the value is true, we will use COUNTA to count the non-blanks in the range C5:I5. Else, IF returns “N/A.”
In cell J5, here’s the formula we entered and then applied to the entire range:
=IF(B5="Employed",COUNTA(C5:I5),"N/A")
Count Occurrence of Texts, Numbers, and Blanks
| Function Used | Data Type | Syntax |
| COUNT | STATISTICAL | =COUNT(value1,[value2]) |
| COUNTA | STATISTICAL | =COUNTA(value1,[value2]) |
| COUNTBLANK | STATISTICAL | =COUNTBLANK(range) |
Excel offers other functions including COUNT, COUNTA, and COUNTBLANK to count numbers, non-blank, and blank cells respectively.
In this sheet, we have the total sales made by employees of a company. If the employee was not present or made no sales on the date of record, the Total sales field was left empty.
As you can see some people are missing their employee ID. We’ve been assigned to count the following:
- Total employee count
- Employees with missing employee IDs
- Employees who have made sales
To count the total number of employees, we entered the following formula in cell E10:
=COUNTA(B5:B76)
We counted the employees with missing IDs using this formula in cell F10:
=COUNTBLANK(A5:A76)
To count the number of employees that made sales, here’s the formula we entered in cell G10:
=COUNT(C5:C76)
Count Occurrences Using PivotTable
A PivotTable is an excellent tool to use when summarizing data. PivotTables are also useful in automating calculations such as summation and count.
If you’ve stored your data as a table or range, you’ll first have to convert it into a PivotTable. Then, we’ll switch the data fields to count occurrences.
Excel usually identifies the operation you’re looking to perform. However, if the operation is not set to Count, you can manually change that.
- Select your data range.
- Go to the Insert tab.
- From the Tables section, click PivotTable.
- In the PivotTable from table or range window, select a destination and click OK.
- From the PivotTable Fields section, check the box next to the item you wish to count.
- Drag and drop the same item under the Values section.
- If the operation is not set to count, click on the fly-out under the Values section > Value Field Settings.
- Under Summarize value field by, choose Count > OK.
- This is how PivotTables counts your data.
