CODEWITHSUNDEEP
vbaexcelexcel-formula
Stuart1044
Stuart1044

Reputation: 444

Counting Distinct Values with multiple criteria

I have an Excel file with the below data example:

IssueNumber    Client
100            Client 1
100            Client 1
101            Client 1
102            Client 2
102            Client 2

I want to count the number of unique IssueNumbers for each client. So the end result would be:

Client    Count
Client 1  2
Client 2  1

I have a list of the clients in a separate tab from the main data, and am trying to look up using COUNTIFS, and passing the Client name as one of the criteria, but I am struggling to figure out how to count the unique issues.

This must be really simple, but it is Friday afternoon and my brain has given up!

Upvotes: 1

Views: 2850

Answers (2)

Mrig
Mrig

Reputation: 11727

Few more options:

=SUM(IF(((MATCH($A$2:$A$6&$B$2:$B$6,$A$2:$A$6&$B$2:$B$6,0))>=(ROW($A$2:$A$6)-(MIN(ROW($A$2:$A$6))-1)))*($B$2:$B$6=D2)=1,1,0))

&

=SUM(IF(FREQUENCY(IF($B$2:$B$6=D2,MATCH($A$2:$A$6&"_"&$B$2:$B$6,$A$2:$A$6&"_"&$B$2:$B$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),1))

Both the above formulas are array formula so should be entered by pressing Ctrl+Shift+Enter.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

Its Friday Morning here so I am not so burnt out yet,

=SUMPRODUCT(($B$2:$B$6=D2)*1/COUNTIF($A$2:$A$6,$A$2:$A$6))

enter image description here

Upvotes: 3

Related Questions