Abstract
Transcript
In this video, we'll look at how to count the unique values returned by the UNIQUE function.
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.
