Subtotal by rate within class

  • Thread starter Thread starter Tat
  • Start date Start date
T

Tat

I would like to create subtotals for each change in rate
for each specific class.

Class Rate
OT 30.00
OT 60.00
OT 60.00
PT 15.00
PT 15.00
PT 50.00

I would like the subtotals do be as follows:
OT 30.00
OT 120.00
PT 30.00
PT 50.00

Is this possible?
 
Assuming that A1:B7 houses the sample (with labels) you provided.

Apply Advanced Filter to A1:B7 with "Unique records only" checked, such that
the result of filtering is say in: D1:E5...

{"Class","Rate";"OT",30;"OT",60;"PT",15;"PT",50}

In F1 enter: Subtotal.

In F2 enter & copy down:

=SUMPRODUCT(--($A$2:$A$7=D2),--($B$2:$B$7=E2),$B$2:$B$7)
 
That worked great! Thanks. I would like to use this as a
macro on any spreadsheet with rows ='x'. Meaning, the size
of the spreadsheet will vary with the number of records
each time I run this report. Can I put this in a macro
where the PC will detect the number of rows in the
spreadsheet so that it can determine the range for the
filter? ie some spreadsheets are A1:B7, others can be
A1:B89, A1:B5. Can this process be done generically no
matter what the size of the spreadsheet is?
 
That worked great! Thanks. I would like to use this as a
macro on any spreadsheet with rows ='x'. Meaning, the size
of the spreadsheet will vary with the number of records
each time I run this report. Can I put this in a macro
where the PC will detect the number of rows in the
spreadsheet so that it can determine the range for the
filter? ie some spreadsheets are A1:B7, others can be
A1:B89, A1:B5. Can this process be done generically no
matter what the size of the spreadsheet is?
Click to expand...

I think you can find macro's on the newsgroups (thru Google) that do what
you require. If you're interested in creating the required unique list using
formulas, see:

http://tinyurl.com/yquuo
 
Back
Top