Countifs for multiple and unique data

[RMSmith3]

Hi Everyone,

I've been puzzled with this one for way to long and am hoping someone will be able to help!

Below is an example of the data I'm using:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[/TR]
[TR]
[TD]Rob[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Type[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]25/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]05/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]04/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]29/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]14/04/2014[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2014[/TD]
[/TR]
[TR]
[TD="align: right"]28/05/2014[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2014[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]This Q?[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


So from this data table I want to create a formula which tells me how many unique names (column A) there are, which are Red (column B) and in this quarter (columns C&D).


What I've got so far is:

To count the unique entries in col A:

=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

To count the unique entries in column a, where column b is red

=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)*(B2:B11="Red"))



But when I tried to add in the "This Q" column I get a number which is .33333!?


My Question:

So am I completely off the mark on how to do this? or am I making a simple mistake?

Would be incredibly grateful if someone could help!


Thanks,

Rob

 

[rilzniak]

=SUM(IF(FREQUENCY(IF(D2:D11="Yes",IF(B2:B11="Red",IF(A2:A11<>"",MATCH(A2:A11,A2:A11,0)))),ROW(A2:A11)-ROW(A2)+1),1))

Enter with Ctrl+Shift+Enter