Solved

Count unique values based on several criteria

Posted on 2011-09-27
6
240 Views
Last Modified: 2012-05-12
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
Comment
Question by:teaone
6 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36710348
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 Comment

by:teaone
ID: 36710414
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36710449
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36710464
Could you not just use COUNTIFS?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36710512
Not to count unique entries in column F, no.
0
 

Author Comment

by:teaone
ID: 36710651
thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now