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

#### JDsmithuk

##### New Member
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>

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Example:

Excel 2010
ABC
1ProductQtyWeek No
2XYZ102014-01
3XYZ82014-02
4XYZ92014-01
5Fred1002014-01
6Bill852014-01
7
8Unique products sold2014-013
Sheet1
Cell Formulas
RangeFormula
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.

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

Excel 2010
ABCD
1ProductQtyWeek NoY/N
2XYZ102014-01Yes
3XYZ82014-02Yes
4XYZ92014-01Yes
5Fred1002014-01Yes
6Bill852014-01No
7
8Unique products sold2014-012Yes
Sheet1
Cell Formulas
RangeFormula
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.

Perfect thanks!

Replies
1
Views
86
Replies
8
Views
111
Replies
3
Views
302
Replies
0
Views
2K
Replies
6
Views
384

1,203,727
Messages
6,056,966
Members
444,899
Latest member
Excel_Temp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back