• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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
Asked:
teaone
1 Solution
 
andrewssd3Commented:
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
 
teaoneAuthor 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
 
barry houdiniCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
andrewssd3Commented:
Could you not just use COUNTIFS?
0
 
Rory ArchibaldCommented:
Not to count unique entries in column F, no.
0
 
teaoneAuthor Commented:
thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now