# Count unique values in Subtotal Report

#### rmtimmah

##### Board Regular
Hi,

I have the following subtotals report.
Book1
ABCD
1VendorSpendClass
2SupplierA\$29.011
3SupplierA\$2,357.181
4SupplierA\$530.291
5SupplierA\$2,612.011
6SupplierATotal\$5,528.49
7SupplierB\$518.192
8SupplierB\$499.172
9SupplierB\$514.722
10SupplierBTotal\$1,532.08
11SupplierC\$516.531
12SupplierC\$532.941
13SupplierC\$217.002
14SupplierC\$453.992
15SupplierC\$779.992
16SupplierCTotal\$2,500.45
Sheet1

I want to be able to count the number of Classes using each supplier as well as a total spend for eacdh supplier. The subtotal report totals the spend correctly but how do I count the number of classes. ie for Supplier A & B there is only 1 class using this supplier but for Supplier C there are 2. Any ideas?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### stanleydgromjr

##### Banned
rmtimmah,

Excel Workbook
ABCDEFG
1Vendor*Spend*Class*VendorClassCount Of
2Supplier*A*\$********29.01*1*Supplier*A14
3Supplier*A*\$****2,357.18*1*Supplier*A20
4Supplier*A*\$******530.29*1*Supplier*B10
5Supplier*A*\$****2,612.01*1*Supplier*B23
6Supplier*A*Total*\$****5,528.49***Supplier*C12
7Supplier*B*\$******518.19*2*Supplier*C23
8Supplier*B*\$******499.17*2****
9Supplier*B*\$******514.72*2****
10Supplier*B*Total*\$****1,532.08******
11Supplier*C*\$******516.53*1****
12Supplier*C*\$******532.94*1****
13Supplier*C*\$******217.00*2****
14Supplier*C*\$******453.99*2****
15Supplier*C*\$******779.99*2****
16Supplier*C*Total*\$****2,500.45******
Sheet1

The formula in cell G2 (copied down):
=SUMPRODUCT((\$A\$2:\$A\$16=\$E2)*(\$C\$2:\$C\$16=\$F2))

You will have to adjust the ranges (last row) to match your data.

Have a great day,
Stan

#### Yogi Anand

##### MrExcel MVP
Hi rmtimmah:

Following is one way ...
y080615h1.xls
ABCDEF
1VendorSpendClassClass_Frequency
2Supplier A\$ 29.011Supplier A1
3Supplier A\$ 2,357.181Supplier B1
4Supplier A\$ 530.291Supplier C2
5Supplier A\$ 2,612.011
6Supplier A Total\$ 5,528.49
7Supplier B\$ 518.192
8Supplier B\$ 499.172
9Supplier B\$ 514.722
10Supplier B Total\$ 1,532.08
11Supplier C\$ 516.531
12Supplier C\$ 532.941
13Supplier C\$ 217.002
14Supplier C\$ 453.992
15Supplier C\$ 779.992
16Supplier C Total\$ 2,500.45
17
Sheet3

array formula in cell F2 is ...
=SUM(((FREQUENCY(IF(\$A\$2:\$A\$16=E2,\$C\$2:\$C\$16),ROW(\$1:\$15)))>0)+0)

this is then copied down to F3 and F4

Replies
2
Views
254
Replies
1
Views
205
Replies
1
Views
631
Replies
1
Views
149
Replies
3
Views
144

1,191,173
Messages
5,985,090
Members
439,940
Latest member

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