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...
excel
Formulas and Functions
  • ChrisMendoza's avatar
    ChrisMendoza
    Dec 04, 2019

    rcouri15 

    This should calculate based off your sample:

     

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

     

    CTRL + SHIFT + ENTER 

     

     

ChrisMendoza's avatar
ChrisMendoza
Iron Contributor
Dec 04, 2019

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
    Dec 05, 2019

    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
      Dec 05, 2019

      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