Counting Unique Values

[apc1968]

Hi - I have the following set of data:

	A	B	C	D
1	Product	Month	Customer	Value
2
3	Charts	1	AAA	£20
4	Publications	1	BBB	£25
5	Flags	1	AAA	£33
6	Charts	1	AAA	£10
7	Flags	2	BBB	£15
8	Charts	2	AAA	£16

<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

I want to be able to look at how many unique customers bought charts in month 1? In the above table it would be 1 - thanks

 

[anglais428]

=SUM(IF("Charts"=$A$3:$A$8, 1/(COUNTIFS($A$3:$A$8, "Charts", $C$3:$C$8, $C$3:$C$8)), 0))

Confirm with Control Shift Enter

 

[apc1968]

=SUM(IF("Charts"=$A$3:$A$8, 1/(COUNTIFS($A$3:$A$8, "Charts", $C$3:$C$8, $C$3:$C$8)), 0))

Confirm with Control Shift Enter

Thanks - doesnt it need to reference column B so that it only picks up month 1 sales?

Thanks again

 

[ankita.sethi]

I would have added a column F and put in the formula the following formula in each row -
=IF(SUMPRODUCT((B3="Charts")*(C3=1))=1,D3,0)
This will 0's or the name of the Customer depending on the criteria met or not

to calculate final unique count use the following formula -
=SUM(IF(FREQUENCY(F3:F8,F3:F8)>0,1))
This will give you the answer '1' for the data posted