Solved

Count unique values based on several criteria

Posted on 2011-09-27
6
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

688 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