Count Unique values based on multiple criteria
Hi, Happy New Year to you all!!
I am trying to create an excel formula which will count the number of unique customer IDs in row 2 based on the month value (eg "October") in row 12 and the status of their review (eg "Fail") in row 41.
I have been running around this for hours looking at UNIQUE and SUMPRODUCT, SUM/IF/COUNTIF and I cannot get the result I need so I thought I would ask the experts.
Many thanks in advance.
Nick
Best Answer
-
this is what I am currently using to calculate the number of unique values: =SUMPRODUCT(1/COUNTIFS('Agent'!B2:OZ2,'Agent'!B2:OZ2&""))-1
however I need to add in the extra criteria to say only count them if the month is also 'October' and the status is 'Fail'
thanks
Answers
-
Hi @Nick Reynolds,
Have you tried using COUNTIFS
=COUNTIFS([Customer IDs]:[Customer IDs], "October", [Month]:[Month], "Fail", [Status of Review]:[Status of Review])
Hope this helps!
-
Hi Christian
Many thanks for coming back to me however there are multiple instances of the same customer ID and I need to know the number of unique IDs which have failed in October rather than the total number of fails accross all IDs in October.
thanks
-
this is what I am currently using to calculate the number of unique values: =SUMPRODUCT(1/COUNTIFS('Agent'!B2:OZ2,'Agent'!B2:OZ2&""))-1
however I need to add in the extra criteria to say only count them if the month is also 'October' and the status is 'Fail'
thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!