Count unique values if Multiple Criteria are met ?

[tarynmahon]

I need a formula that will do as the title suggests, example data as follows;

A B C D

SUR391PMC ADON Supplier A Web

SUR391PMC ADON Supplier A Web

SUR395PMC PC Supplier B Call Centre

SUR395PMC ADON Supplier B Call Centre

SUR395PMC ADON Supplier B Call Centre

SUR396PMC PC Supplier A Call Centre

SUR402PMC PC Supplier C Call Centre

SUR405PMC PC Supplier D Web

SUR405PMC ADON Supplier D Web

SUR405PMC ADON Supplier D Web


If I was not trying to count unique values in (A) my formula would be;

=COUNTIFS(C:C,"Supplier A",D:D,"Web",B:B,"ADON") - This returns 2

I only want to count 1 per Client Ref in column (A) therefore the result should be 1, Supplier B should return 0

Supplier C should return 0

Supplier D should return 1


Please help me!!!

 

[NARAYANK991]

Hi ,


Try the folowing entered as an array formula , using CTRL SHIFT ENTER :


=SUM(IF((B4:B13="ADD-ON")*(C4:C13="Supplier D")*(D4:D13="Web"),1/COUNTIFS(B4:B13,"ADD-ON",C4:C13,"Supplier D",D4:D13,"Web",A4:A13,A4:A13)), 0)


This technique will work for 1 , 2 , 3 ,... criteria , where unique values from the extra column are required. A4:A13,A4:A13 used twice give this additional facility for retrieving the unique values from that column.


I have assumed your data is as follows , in the range A4:D13

[pre]

SUR391PMC	ADD-ON	Supplier A	Web
SUR391PMC	ADD-ON	Supplier A	Web
SUR395PMC	PC	Supplier B	Call Centre
SUR395PMC	ADD-ON	Supplier B	Call Centre
SUR395PMC	ADD-ON	Supplier B	Call Centre
SUR396PMC	PC	Supplier A	Call Centre
SUR402PMC	PC	Supplier C	Call Centre
SUR405PMC	ADD-ON	Supplier D	Web
SUR405PMC	ADD-ON	Supplier D	Web
SUR405PMC	ADD-ON	Supplier D	Web

[/pre]
Narayan

 

[Montrey]

Excel Ninja. Hiyawwwwww!

 

[tarynmahon]

Im still waiting for this formula to calculate to see if it works but I substituted your data refs with Column refs, Im assuming after waiting about 7 minutes that this formula does not work for column references? ie. A:A rather than A4:A13?

 

[tarynmahon]

I've just tried it with data refs and it works a treat, Montrey is right, you are an Excel Ninja!!!! Thank you so much!

 

[David Epstein]

I used the iip below and modified the forumula to

=SUM(IF((Data!F:F="Teak 3 - Cisco")*(Data!AB:AB>=DATE(2014,10,1))*(Data!AB:AB<=DATE(2014,10,31)),1/COUNTIFS(Data!F:F,"Teak 3 - Cisco",Data!AB:AB,">="&DATE(2014,10,1),Data!AB:AB,"<="&DATE(2014,10,31),Data!B:B,Data!B:B)),0)

... but I am get "0" (zero), when I expect 26.

Simply, the forumula needs to count unique values in column B, where the 3 conditions are met.
Conditon 1 column F = Teak - Cisco
Condition 2 Date>= 10/1/14
Condition 3 Date<= 10/31/14

I assume if is acceptable to use column refereneces, as this sheet is 800 rows. I appreciate you help!!!

Dave

Hi ,


Try the folowing entered as an array formula , using CTRL SHIFT ENTER :


=SUM(IF((B4:B13="ADD-ON")*(C4:C13="Supplier D")*(D4:D13="Web"),1/COUNTIFS(B4:B13,"ADD-ON",C4:C13,"Supplier D",D4:D13,"Web",A4:A13,A4:A13)), 0)


This technique will work for 1 , 2 , 3 ,... criteria , where unique values from the extra column are required. A4:A13,A4:A13 used twice give this additional facility for retrieving the unique values from that column.


I have assumed your data is as follows , in the range A4:D13

[pre]

SUR391PMC    ADD-ON    Supplier A    Web
SUR391PMC    ADD-ON    Supplier A    Web
SUR395PMC    PC    Supplier B    Call Centre
SUR395PMC    ADD-ON    Supplier B    Call Centre
SUR395PMC    ADD-ON    Supplier B    Call Centre
SUR396PMC    PC    Supplier A    Call Centre
SUR402PMC    PC    Supplier C    Call Centre
SUR405PMC    ADD-ON    Supplier D    Web
SUR405PMC    ADD-ON    Supplier D    Web
SUR405PMC    ADD-ON    Supplier D    Web

[/pre]
Narayan

 

[Somendra Misra]

Hi @David Epstein

Welcome to chandoo.org forum.

Can you post a sample file.

Regards,