calculate number of unique values in column where a different column equals x

[JDsmithuk]

Hi I have the data of which an example is below and what I want to do is for eack week calculate the number of different products sold cant get my head around sumproduct with an if or even sure if thats the way to do it

Product	Qty	Week No
XYZ	10	2014-01
XYZ	8	2014-02
XYZ	9	2014-01
Fred	100	2014-01
Bill	85	2014-01
Unique products sold	2014-01	??????? answer 3

<tbody>
</tbody>

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C
1	Product	Qty	Week No
2	XYZ	10	2014-01
3	XYZ	8	2014-02
4	XYZ	9	2014-01
5	Fred	100	2014-01
6	Bill	85	2014-01
7
8	Unique products sold	2014-01	3
Sheet1

Cell Formulas
Range	Formula
C8	{=SUM(IF(FREQUENCY(IF(C2:C6=B8,MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JDsmithuk]

Thank you Andrew thats brilliant can you build on this for example if column D was a completed flag Yes or now could we count unique products for week one when completes was yes and if you could what would that look like?

Thanks again
JD

 

[Andrew Poulsom]

You can add more conditions:

Excel 2010
	A	B	C	D
1	Product	Qty	Week No	Y/N
2	XYZ	10	2014-01	Yes
3	XYZ	8	2014-02	Yes
4	XYZ	9	2014-01	Yes
5	Fred	100	2014-01	Yes
6	Bill	85	2014-01	No
7
8	Unique products sold	2014-01	2	Yes
Sheet1

Cell Formulas
Range	Formula
C8	{=SUM(IF(FREQUENCY(IF(C2:C6=B8,IF(D2:D6=D8,MATCH(A2:A6,A2:A6,0))),ROW(A2:A6)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JDsmithuk]

Perfect thanks!