Sum table Totals

[brandon16]

Hi,

I have a pivot table which gives me exactly what I need until being asked with summing up the Cost variable by the JD Variable.

The table below, variable Total Job Cost, has been hard coded to hopefully help give an understanding of that it is I am after.

Any idea how I can create this variable please? I tried to do a vlookup sum on the but it didn't work.

For each job split by week I sum Hours * Charge to populate Cost. If this isn't clear, please let me know.

Thanks in advance.

[TABLE="width: 774"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]JD[/TD]
[TD]Name[/TD]
[TD]Date by Week[/TD]
[TD]Hours[/TD]
[TD]Charge[/TD]
[TD]Cost [/TD]
[TD]Total Job Cost[/TD]
[/TR]
[TR]
[TD="align: right"]111111[/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]30/10/2017 - 05/11/2017[/TD]
[TD="align: right"]9[/TD]
[TD]£10[/TD]
[TD]£90[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]06/11/2017 - 12/11/2017[/TD]
[TD="align: right"]14[/TD]
[TD]£10[/TD]
[TD]£140[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]13/11/2017 - 19/11/2017[/TD]
[TD="align: right"]37[/TD]
[TD]£10[/TD]
[TD]£370[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]20/11/2017 - 26/11/2017[/TD]
[TD="align: right"]16[/TD]
[TD]£10[/TD]
[TD]£160[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]27/11/2017 - 03/12/2017[/TD]
[TD="align: right"]12.5[/TD]
[TD]£10[/TD]
[TD]£125[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]04/12/2017 - 10/12/2017[/TD]
[TD="align: right"]10.25[/TD]
[TD]£10[/TD]
[TD]£103[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Man[/TD]
[TD]Barry[/TD]
[TD]11/12/2017 - 17/12/2017[/TD]
[TD="align: right"]37.5[/TD]
[TD]£10[/TD]
[TD]£375[/TD]
[TD]£1,363[/TD]
[/TR]
[TR]
[TD="align: right"]222222[/TD]
[TD]Lab[/TD]
[TD]Gaz[/TD]
[TD]27/11/2017 - 03/12/2017[/TD]
[TD="align: right"]1.75[/TD]
[TD]£32[/TD]
[TD]£56[/TD]
[TD]£56[/TD]
[/TR]
[TR]
[TD="align: right"]333333[/TD]
[TD]Ope[/TD]
[TD]Paul[/TD]
[TD]20/11/2017 - 26/11/2017[/TD]
[TD="align: right"]10[/TD]
[TD]£22[/TD]
[TD]£220[/TD]
[TD]£473[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Ope[/TD]
[TD]Paul[/TD]
[TD]27/11/2017 - 03/12/2017[/TD]
[TD="align: right"]11.5[/TD]
[TD]£22[/TD]
[TD]£253[/TD]
[TD]£473[/TD]
[/TR]
</tbody>[/TABLE]

 

[dfsobral]

if i understood what you want this should work

being JD column B and Cost column G
copy to column K this formula
=SUMPRODUCT(G:G, --(B:B=J1))

and write in column J the JD reference

adapt as needed

 

[brandon16]

Brilliant, that seems to have worked, thank you!