To create a dynamic summary count with a formula, you can use the CHOOSE, UNIQUE, and COUNT functions to return unique values.
How to create a dynamic summary count in Excel
Here are the steps to create a unique count of items in Excel:
- Select cell D3.
- Type =CHOOSE({1,2},UNIQUE(data),COUNTIF(data,UNIQUE(data))) in cell D3, assuming “data” represents your source range (e.g., B3:B10).
- Press Enter.
- The formula will display a list of unique items from the “data” range in one column and the count of each item in the adjacent column.
Example
In this example, we have a dataset in range B3:B10 containing a list of items, some of which are repeated. The goal is to create a summary table displaying each unique item and its count. The formula in cell D3 uses the UNIQUE function to extract distinct items from the dataset and combines it with the COUNTIF function to calculate the frequency of each unique item.
We use an Excel table. Select the range and use the Ctrl + T shortcut. After that, select the range again, click the name box and add a name to a table, in this case, “data“.
It’s very important to note that if you delete, modify, or add new rows to the data table, the result will automatically update, meaning it will always remain dynamic.
Formula:
=CHOOSE({1,2},UNIQUE(data),COUNTIF(data,UNIQUE(data)))
The formula returns a unique list of items from the range data and counts the occurrences of each unique item, arranging them in a two-column array.
Explanation
Let’s see the detailed explanation and evaluate the formula.
UNIQUE(data):
The UNIQUE function extracts all unique items from the specified range, data. For example, if data contains values like {“Apple”, “Banana”, “Melon”, “Banana”, “Kiwi”, “Apple”, “Banana”}, UNIQUE(data) will return an array {“Apple”, “Banana”, “Melon”, “Kiwi”}, listing each distinct item once.
COUNTIF(data, UNIQUE(data)):
COUNTIF(data, UNIQUE(data)) counts how often each unique item appears in the data range. Here, COUNTIF uses the unique items as criteria, effectively counting occurrences of “Apple,” “Banana,” “Melon,” and “Kiwi” in data. For our example, it would return {2, 4, 1, 1} since “Apple” appears twice, “Banana” four times, “Melon” once, and “Kiwi” once.
CHOOSE({1,2}, …):
CHOOSE combines the results from the previous steps into a two-dimensional array. The {1,2} array specifies that CHOOSE should create two “columns”: the first will take values from UNIQUE(data) (unique items), and the second from COUNTIF(data, UNIQUE(data)) (counts). Thus, CHOOSE({1,2}, UNIQUE(data), COUNTIF(data, UNIQUE(data))) constructs an array like: {“Apple”, 2; “Banana”, 4; “Melon”, 1; “Kiwi”, 1}.
This array will “spill” into the worksheet, with each unique item paired with its count in adjacent columns.
Workaround with HSTACK function
Using HSTACK instead of CHOOSE simplifies the formula and achieves the same dynamic, automatically updating behavior. When you add, delete, or modify rows in data, the output will refresh instantly.
Formula:
=HSTACK(UNIQUE(data), COUNTIF(data, UNIQUE(data)))
HSTACK horizontally stacks the arrays generated by UNIQUE(data) and COUNTIF(data, UNIQUE(data)), placing them side by side in two columns. The result is a two-column dynamic array where the first column lists each unique item, and the second column shows the count of each item.