How to count the number of occurrences in Excel

Excel has very powerful features for handling text, numeric and alphanumeric data as well. It has many functions not only for computation but also for visualization and data analysis. Sometimes, while handling large datasets we need to know how many times a specific text or a number appears in a data range. That’s when you need to count the number of occurrences of a specific text or number in Excel.

In this tutorial we’ll learn how to count the number of occurrences of a specific text or number in Excel, by using the following two methods.

• Using COUNTIF
• Using SUM along with IF function

Let’s take a look at the following sales dataset above. It has sales records of various items from various cities. We would like to count how many invoices belong to a particular sales rep.

Method 1: Use COUNTIF to count the number of occurrences

COUNTIF is a very useful function that can be easily used to count the occurrences of a particular text or a number value in a big data set. Let’s see how to use it on our dataset to count the number of invoices belonging to a particular sales rep.

COUNTIF has a very simple syntax which is presented below,

COUNTIF(range, criteria)

range:

The data range to look for a particular text or number value.

criteria:

This parameter specifies what we are going to look for, it could be any simple text e.g., “Text”, any number value “10” or a logical check for example “>50” within double quotes only.

Step 1 – Create a formula using COUNTIF and implement it

• We’ll use the following simple formula to determine the number of occurrences of a sales rep named “Sofia” within our dataset.

=COUNTIF(D5:D37,H5)

H5 contains the name Sofia. We did not hard code it to keep the formula dynamic. This way we can change the name in the cell H5 and get the count for that name easily as shown above.

Method 2: Use SUM with IF to count the number of occurrences

The combination of SUM function with IF is quite handy when it comes to count the occurrences of a particular text or a number value in a big data set. Let’s see how to use it on our dataset to count the number of invoices belonging to a particular sales rep. 

The syntax of SUM is very simple,

SUM(number1, [number2],….)

At least one argument is required to calculate the sum and you add as many cells you wish to add separated by a comma. A range can also be entered instead of individual cells e.g., A1:A10

The syntax of IF is as follows,

IF(logical_test, [value_if_true], [value_if_false])

This function requires the following three parameters, which are explained below one by one.

• logical_test:

The first parameter is the logical test condition which will be evaluated by the formula. When constructing a test condition, we can use the following logical operators to evaluate a logical scenario;

• = (equal to)
• > (greater than)
• >= (greater than or equal to)
• < (less than)
• <= (less than or equal to)
• <> (not equal to)

• value_if_true:

The formula evaluates the logical condition and if the condition is found correct (true) then this value will be chosen to appear as a result.

• value_if_false:

This is the value which will be chosen as a result, when the test condition is found incorrect (false) after being evaluated in the formula.

The IF function will check the range for the condition and will generate a 1 for every match and 0 for every mismatch. Finally, the SUM will add all the values and we will get the count of the number of occurrences of the sales rep.

Step 1 – Create a formula using SUM and IF functions 

• We’ll use the following simple formula to determine the number of occurrences of a sales rep named “Sofia” within our dataset.

=SUM(IF(D5:D37,H5,1,0))

H5 contains the name Sofia. We did not hard code it to keep the formula dynamic. This way we can change the name in the cell H5 and get the count for that name easily as shown above.