Before dynamic array formulas, counting unique values in Excel involved complex array formulas, especially if you needed to count values based on one or more conditions. Now that dynamic array formulas, and especially the UNIQUE function, are part of Excel, this is all much easier. On this first worksheet, this list of colors contains duplicates. We want to get a count of the unique colors. Now, I can easily count these values with the COUNTA function. The COUNTA function will return a count of both numbers and text - basically a count of all non-blank cells. In this case, COUNTA returns 12, since there are 12 cells in the range, and all have values. Next, I’ll enter the UNIQUE function in D5. When I provide the full range to UNIQUE, you can see we have 5 unique colors total. Now, to get a unique count, I just need to feed these results to the COUNTA function. I do that by wrapping COUNTA around the UNIQUE function If I check the result passed into COUNTA by UNIQUE, we see the 5 colors. And, when I enter the formula, COUNTA returns 5. On the next worksheet, we have time logged by several people for two projects, Alpha and Omega. This data is in an Excel Table called “Time” Let’s count how many unique people worked on each project. I’ll start with a total in J5. As before, I’ll give UNIQUE the full set of names. Then I’ll pass that result into COUNTA, which returns 4. Now, to get the count of people who have worked on Omega, I need to first use the FILTER function. Array is the full list of names, the include argument is all projects equal to Omega in cell H4. Now I can wrap the UNIQUE function around FILTER. This represents the list of people who have worked on Omega. Finally, to get a count, I wrap COUNTA around the whole formula. To get a count for the Alpha project, I’ll start with the same formula, then change the reference from H4 to G4. To recap how this formula works, I’ll use the F9 key. FILTER gets a list of names for the project. UNIQUE takes that list and returns unique values only. Finally, COUNTA returns the count. Last, to prove that the formulas are dynamic, if I add a new name to the end of the table, the counts update.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
