x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 260

# Count unique values based on several criteria

I need to count unique text values based on five criteria. For example, I have my text values in cells F2:F10 and I need to get a unique count of those values if value in A2:A10 = "A", in B2:B10 = "B", in C2:C10 = "C", in D2:D10 = "D", and in E2:E10 = "E". There could be blank values in column F, they need to be ignored.
0
teaone
1 Solution

Commented:
I don't quite understand the question - it would help to have sample file showing the data and the results you expect, if possible.
0

Author Commented:
the regular SUMIFS formula, if I had numeric values in column F, would look like this: =SUMIFS(F:F,A:A,"A",B:B,"B",C:C,"C",D:D,"D",E:E,"E"). Instead, I have text values and need to get a unique count of those.
0

Commented:
Try this formula

=SUM(IF(FREQUENCY(IF((A2:A10="A")*(B2:B10="B")*(C2:C10="C")*(D2:D10="D")*(E2:E10="E")*(F2:F10<>""),MATCH(F2:F10,F2:F10,0)),ROW(F2:F10)-ROW(F2)+1),1))

confirmed with CTRL+SHIFT+ENTER

see attached - formula in H2 - column F values can be any data type

regards, barry
count-unique.xls
0

Commented:
Could you not just use COUNTIFS?
0

Commented:
Not to count unique entries in column F, no.
0

Author Commented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.