Count unique values in pivot table or with formula

iwian

New Member
Joined
Dec 6, 2017
Messages
1
Hello, how can i count unique TO-Numbers for every store from the example
Pivot return 16 for S10 but TO-numbers are 2, and product numbers are 9
Is it possible to be counted in other sheet with formula?

TO-0100a001S10
TO-0100a002S10
TO-0100a003S10
TO-0100a004S10
TO-0100a005S10
TO-0100a006S10
TO-0100a007S10
TO-0101a001S11
TO-0101a002S11
TO-0101a003S11
TO-0101a004S11
TO-0101a005S11
TO-0101a006S11
TO-0101a007S11
TO-0101a008S11
TO-0101a009S11
TO-0101a010S11
TO-0101a011S11
TO-0102a001S10
TO-0102a002S10
TO-0102a003S10
TO-0102a004S10
TO-0102a005S10
TO-0102a006S10
TO-0102a007S10
TO-0102a008S10
TO-0102a009S10

<colgroup><col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> <col style="mso-width-source:userset;mso-width-alt:6144;width:126pt" width="168"> <col style="mso-width-source:userset;mso-width-alt:4973;width:102pt" width="136"> </colgroup><tbody>
[TD="width: 78"]TO-number[/TD]
[TD="width: 110"]product-number[/TD]
[TD="width: 94"]store-number[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 92"]Row Labels[/TD]
[TD="width: 168"]Count of product-number[/TD]
[TD="width: 136"]Count of TO-number[/TD]

[TD="class: xl63"]S10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[TD="class: xl63"]S11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]

[TD="class: xl63"]Grand Total[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]

</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Below is a formula way. This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also here is a link to using a pivot table. See second answer there is a "202" by it.
https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values
Excel Workbook
ABCDEFGH
1TO-numberproduct-numberstore-numberRow LabelsCount of product-numberCount of TO-number
2TO-0100a001S10S1029
3TO-0100a002S10S11111
4TO-0100a003S10Grand Total
5TO-0100a004S10
6TO-0100a005S10
7TO-0100a006S10
8TO-0100a007S10
9TO-0101a001S11
10TO-0101a002S11
11TO-0101a003S11
12TO-0101a004S11
13TO-0101a005S11
14TO-0101a006S11
15TO-0101a007S11
16TO-0101a008S11
17TO-0101a009S11
18TO-0101a010S11
19TO-0101a011S11
20TO-0102a001S10
21TO-0102a002S10
22TO-0102a003S10
23TO-0102a004S10
24TO-0102a005S10
25TO-0102a006S10
26TO-0102a007S10
27TO-0102a008S10
28TO-0102a009S10
Sheet
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top