Excel is a powerful and versatile tool that can perform various calculations and analysis on data. One of the common tasks that Excel users may encounter is to count how many times a specific string appears in a range of cells. For example, you may want to know how many different names, products, or categories are in a list of sales data.
There are different ways to count unique occurrences of specific strings in Excel, depending on the type and format of the data. In this article, we will explore some of the most popular and effective methods, and explain the basic theory and procedures behind them. We will also provide a scenario to illustrate each method with real numbers and an Excel table.
Method 1: Using SUM and COUNTIF Functions
One of the easiest ways to count unique occurrences of specific strings in Excel is to use the SUM and COUNTIF functions. The SUM function returns the sum of a list of numbers, and the COUNTIF function counts the number of cells that meet a certain criterion. By combining these two functions, we can count how many times each string appears in a range, and then sum up the inverse of those counts to get the number of unique strings.
Basic Theory
The basic formula for this method is:
=SUM (1/COUNTIF (range,range))
Where range is the cell range that contains the strings you want to count.
The formula works with the following logic:
- The COUNTIF function returns an array of numbers that indicate how many times each string appears in the range. For example, if the range contains {“apple”,”apple”,”banana”,”orange”,”orange”}, the COUNTIF function will return {2,2,1,2,2}.
- The 1/COUNTIF part divides 1 by each number in the array, and returns another array of numbers that represent the inverse of the counts. For example, if the array is {2,2,1,2,2}, the 1/COUNTIF part will return {0.5,0.5,1,0.5,0.5}.
- The SUM function adds up all the numbers in the array, and returns the total value. For example, if the array is {0.5,0.5,1,0.5,0.5}, the SUM function will return 3.
The final result is the number of unique strings in the range.
Procedure
To use this method, follow these steps:
- Select a cell where you want to display the result.
- Enter the formula =SUM (1/COUNTIF (range,range)), and replace range with the cell reference of the range that contains the strings you want to count. For example, if the range is B2:B10, enter =SUM (1/COUNTIF (B2:B10,B2:B10)).
- Press Ctrl + Shift + Enter to enter the formula as an array formula. This is necessary because the formula returns an array of values, not a single value. You should see curly braces {} around the formula in the formula bar, indicating that it is an array formula.
- The result will show the number of unique strings in the range.
Scenario
Suppose you have a list of sales data in Excel, and you want to count how many different salespersons are in the list. The data is in the range A2:B10, as shown below:
| Salesperson | Sales |
|---|---|
| Alice | 100 |
| Alice | 150 |
| Bob | 200 |
| Alice | 120 |
| Charlie | 180 |
| Bob | 160 |
| Charlie | 190 |
| David | 140 |
| Eve | 130 |
To count the unique salespersons, you can use the following formula in cell D2:
=SUM (1/COUNTIF (A2:A10,A2:A10))
Press Ctrl + Shift + Enter to enter the formula as an array formula. The result will show 4, meaning there are 4 different salespersons in the list.
Method 2: Using SUMPRODUCT and COUNTIF Functions
Another way to count unique occurrences of specific strings in Excel is to use the SUMPRODUCT and COUNTIF functions. The SUMPRODUCT function returns the sum of the products of corresponding numbers in one or more arrays, and the COUNTIF function counts the number of cells that meet a certain criterion. By using these two functions together, we can achieve the same result as the previous method, but without entering an array formula.
Basic Theory
The basic formula for this method is:
=SUMPRODUCT (1/COUNTIF (range,range))
Where range is the cell range that contains the strings you want to count.
The formula works with the same logic as the previous method, except that the SUMPRODUCT function can handle arrays without entering an array formula. Therefore, the formula is simpler and faster than the SUM and COUNTIF method.
Procedure
To use this method, follow these steps:
- Select a cell where you want to display the result.
- Enter the formula =SUMPRODUCT (1/COUNTIF (range,range)), and replace range with the cell reference of the range that contains the strings you want to count. For example, if the range is B2:B10, enter =SUMPRODUCT (1/COUNTIF (B2:B10,B2:B10)).
- Press Enter to enter the formula. You do not need to press Ctrl + Shift + Enter, because the formula is not an array formula.
- The result will show the number of unique strings in the range.
Scenario
Using the same sales data as the previous scenario, you can use the following formula in cell D2 to count the unique salespersons:
=SUMPRODUCT (1/COUNTIF (A2:A10,A2:A10))
Press Enter to enter the formula. The result will show 4, meaning there are 4 different salespersons in the list.
Method 3: Using UNIQUE and COUNTA Functions
A third way to count unique occurrences of specific strings in Excel is to use the UNIQUE and COUNTA functions. The UNIQUE function returns an array of unique values from a range or an array, and the COUNTA function counts the number of cells that are not empty. By using these two functions together, we can count how many unique values are in a range or an array.
Basic Theory
The basic formula for this method is:
=COUNTA (UNIQUE (range))
Where range is the cell range or array that contains the strings you want to count.
The formula works with the following logic:
- The UNIQUE function returns an array of unique values from the range or array. For example, if the range contains {“apple”,”apple”,”banana”,”orange”,”orange”}, the UNIQUE function will return {“apple”,”banana”,”orange”}.
- The COUNTA function counts the number of elements in the array, and returns the total value. For example, if the array is {“apple”,”banana”,”orange”}, the COUNTA function will return 3.
The final result is the number of unique strings in the range or array.
Procedure
To use this method, follow these steps:
- Select a cell where you want to display the result.
- Enter the formula =COUNTA (UNIQUE (range)), and replace range with the cell reference or array constant of the range or array that contains the strings you want to count. For example, if the range is B2:B10, enter =COUNTA (UNIQUE (B2:B10)).
- Press Enter to enter the formula. You do not need to press Ctrl + Shift + Enter, because the formula is not an array formula.
- The result will show the number of unique strings in the range or array.
Scenario
Using the same sales data as the previous scenarios, you can use the following formula in cell D2 to count the unique salespersons:
=COUNTA (UNIQUE (A2:A10))
Press Enter to enter the formula. The result will show 4, meaning there are 4 different salespersons in the list.
