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:

Name Chris John Adam Chris Rob Adam Tom Dave Sam Max <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

Type Red Blue Red Blue Blue Red Blue Red Red Blue <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

Date 01/01/2014 25/01/2014 05/02/2014 04/03/2014 29/03/2014 14/04/2014 02/05/2014 28/05/2014 21/06/2014 23/06/2014 <colgroup><col></colgroup><tbody> </tbody>

This Q? Yes Yes Yes Yes Yes No No No No No <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

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