• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
103 views

Count unique values (two columns of data)

PMBO asked on

I need to count the X's in column B only if the values in Column A are unique.

The example below would result in a count of 2 (one X for 5262, and one X for 6520)

I tried several formulas (sumproduct, countifs) but can't get the correct answer.

5262      X
6520  
6520       X
6520       X
6520       X
6520       X

1 person had this question

Abuse history


The answered status icon Answer
ediardp replied on

Hi,

This formula has to be entered as an array

=SUM(IF(FREQUENCY(IF(B1:B9="x",IF(A1:A9<>"",MATCH("~"&A1:A9,A1:A9&"",0))),ROW(A1:A9)-ROW(A1)+1),1))

with the formula edited press CTRL+SHIFT+ENTER

If this post is helpful or answers the question, please mark it so, thank you.
Be the first person to mark this helpful

Abuse history


progress