Forum Discussion
Count the Number of Unique Values That Also Match a Value in Another Column
For example, I have a sheet to track the number of times people donated money. People occur in multiple rows in a table because they donate different amounts. I'm trying to count the number of unique people who donated who also have brown hair. This is a little different than what I'm exactly trying to do but basically the same. I'm attaching an example. I'm able to successfully do it by adding some extra columns but I'm wondering if there is a way to do it without the extra columns (Column B and C in the example), either by nesting formulas or using an array formula. This is a little over my head so would appreciate any help.
This should calculate based off your sample:
=SUM(IF(E5:E14=B2,1/COUNTIFS(B5:B14,B5:B14)))
CTRL + SHIFT + ENTER
3 Replies
- ChrisMendozaIron Contributor
This should calculate based off your sample:
=SUM(IF(E5:E14=B2,1/COUNTIFS(B5:B14,B5:B14)))
CTRL + SHIFT + ENTER
- rcouri15Copper Contributor
ChrisMendoza This works!!! Thank you so much!
Can you break down how this works? It's a little above my head. What does putting 1/COUNTIFS do?
- ChrisMendozaIron Contributor
rcouri15 -
Sorry, I Googled it. A quick look at https://www.extendoffice.com/documents/excel/4091-excel-count-unique-values-based-on-multiple-criteria.html gave me enough to have the framework.
I believe this is what is occurring:
The Green border is evaluated first; the count of values within the range
The Gold border is calculated next; 1/2; 1/2; 1/1; etc.