Timesheet cost code grouping

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

First Sheet

SITE MILES PARKING AMOUNT COST CODE
PC713 6 0 2.190 5-1091
PC713 6 0 2.190 5-1091
PC740 6 0 2.190 5-1538
DIA 9 3 3.285 5-1544
PC740 6 0 2.190 5-1538

Second Sheet

COST CODE TOTAL AMOUNT
5-1091 4.380
5-1538 4.380
5-1544 6.285

I need a way to get the subtotals by Cost Code and put
them into the total amount cell for the corresponding cost
code on a second sheet.

The sites on the first sheet are entered in date of visit
order and will not always be grouped together by site ID
or cost code.

Nick
 
Nick said:
First Sheet

SITE MILES PARKING AMOUNT COST CODE
PC713 6 0 2.190 5-1091
PC713 6 0 2.190 5-1091
PC740 6 0 2.190 5-1538
DIA 9 3 3.285 5-1544
PC740 6 0 2.190 5-1538

Second Sheet

COST CODE TOTAL AMOUNT
5-1091 4.380
5-1538 4.380
5-1544 6.285

I need a way to get the subtotals by Cost Code and put
them into the total amount cell for the corresponding cost
code on a second sheet.

The sites on the first sheet are entered in date of visit
order and will not always be grouped together by site ID
or cost code.

Nick
Click to expand...

As an example, the formula for total amount for cost code 5-1091 would be
something like
=SUMPRODUCT((Sheet1!$E$2:$E$6="5-1091")*Sheet1!$D$2:$D$6)
or
=SUMPRODUCT((Sheet1!$E$2:$E$6=A2)*Sheet1!$D$2:$D$6)
if A2 contains "5-1091"
(This can then be copied down for the totals for your other cost codes.)
Adjust ranges as necessary for your actual data.
 
Back
Top