Forum Discussion

rcouri15's avatar
rcouri15
Copper Contributor
Dec 04, 2019
Solved

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. 

  • rcouri15 

    This should calculate based off your sample:

     

    =SUM(IF(E5:E14=B2,1/COUNTIFS(B5:B14,B5:B14)))

     

    CTRL + SHIFT + ENTER 

     

     

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Iron Contributor

    rcouri15 

    This should calculate based off your sample:

     

    =SUM(IF(E5:E14=B2,1/COUNTIFS(B5:B14,B5:B14)))

     

    CTRL + SHIFT + ENTER 

     

     

    • rcouri15's avatar
      rcouri15
      Copper 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? 

      • ChrisMendoza's avatar
        ChrisMendoza
        Iron 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.

         

Resources