Improve company productivity with a Business Account.Sign Up

x
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
andrewssd3Commented:
Could you not just use COUNTIFS?
0
 
Rory ArchibaldCommented:
Not to count unique entries in column F, no.
0
 
teaoneAuthor 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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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