Count unique values in Subtotal Report

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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