Excel - Count unique values on multiple criteria

Question

My head is just sort of spinning on this and the solution examples. Can someone please provided a solution for this specific issue? Once I see it for what I need then it clicks on how to use elsewhere with different variables. Learning disorder of sorts. Thank you.

A       B       C        D           E
First   Last    Account Transaction  Status
Frank   Tomas   101     ACH          Fraud
Dillon  Krege   105     CC-DC   
Joann   Krimble 103     ACH 
Frank   Tomas   101     Wire    
Clint   Edwards 102     ACH         Fraud
Joann   Krimble 103     CC-DC       Fraud
Joann   Krimble 103     ACH

Unique Account (column C) using Transaction (column D) of "ACH" and coded as Status (column E) of "Fraud"?

There are 3000+ rows in this sheet, if that makes a difference. Pivot table not an option, which I could have done quickly.

ugghhh. The formatting in the box didn't work. Let me take a pic — Dave E
– Dave E, Commented Jul 5, 2018 at 20:44
Please roll back the edit to put the text back in. We cannot copy paste a photo. — Scott Craner
– Scott Craner, Commented Jul 5, 2018 at 20:48

Scott Craner · Accepted Answer · 2018-07-05 20:56:46Z

1

use:

=SUMPRODUCT(((D2:D8="ACH")*(E2:E8="FRAUD"))/(COUNTIFS(C:C,C2:C8,D:D,"ACH",E:E,"FRAUD")+(D2:D8<>"ACH")+(E2:E8<>"FRAUD")))

answered Jul 5, 2018 at 20:56

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2018-07-06 01:00:16Z

1

This is essentially the same method as the response from Scott Craner but incorporates dynamic column length.

=SUMPRODUCT(((D$2:INDEX(D:D, MATCH(1E+99, C:C))=H2)*(E$2:INDEX(E:E, MATCH(1E+99, C:C))=I2))/
            (COUNTIFS(C$2:INDEX(C:C, MATCH(1E+99, C:C)), C$2:INDEX(C:C, MATCH(1E+99, C:C)),
                      D$2:INDEX(D:D, MATCH(1E+99, C:C)), H2, E$2:INDEX(E:E, MATCH(1E+99, C:C)), I2)+
            (D$2:INDEX(D:D, MATCH(1E+99, C:C))<>H2)+(E$2:INDEX(E:E, MATCH(1E+99, C:C))<>I2)))

This formula's dynamic column length does depend on the accounts being true numbers.

edited Jul 6, 2018 at 1:00

answered Jul 6, 2018 at 0:09

user4039065

Collectives™ on Stack Overflow

Excel - Count unique values on multiple criteria

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related