Help with a Workbook issue

N

NoelJ

New Member
Joined
Aug 8, 2019
Messages
2
Good morning
I have a workbook which has 9 individual Dept sheets to record travel costs. I am looking for help in calculating the total monthly cost on each sheet and transfering that figure into the correct month on the summary sheet.

[TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt" span="7"> </colgroup><tbody>[TR]
[TD="width: 448, bgcolor: transparent, colspan: 7"]Travel Costs - Dept 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Month[/TD]
[TD="bgcolor: transparent"]Bus[/TD]
[TD="bgcolor: transparent"]Train[/TD]
[TD="bgcolor: transparent"]Taxi[/TD]
[TD="bgcolor: transparent"]Flights[/TD]
[TD="bgcolor: transparent"]Car Hire[/TD]
[TD="bgcolor: transparent"]Total Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]195[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]247[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]79[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]352[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]378[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]45[/TD]
[TD="bgcolor: transparent, align: right"]93[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]198[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]215[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]75[/TD]
[TD="bgcolor: transparent, align: right"]93[/TD]
[TD="bgcolor: transparent, align: right"]172[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 7"]Monthly Total Cost - Summary Sheet[/TD]
[/TR]
[TR]
[TD]Dept[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Dept 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]

[TR]
[TD]I am trying to search for the correct month entries, total them then transfer that figure to the summary sheet.
Is this possible or am I asking the impossible?
I have tried IF,SUMIF,DGET and VLOOKUP but nothing seems to work, but that could be me! Could you help at all?
Many thanks
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]

</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
How about


Book1
ABCDEFG
1MonthBusTrainTaxiFlightsCar HireTotal Cost
2Apr1042195247
3Apr81523
4Apr325
5May136679
6May22
7May26352378
8Jun52530
9Jun725164593
10Jul1515
11Jul89198215
12Jul32427
13Aug62127
14Aug47593172
15Sept91322
16Sept1414
17Sept86529102
18Sept2224266
Travel Costs - Dept 1



Book1
ABCDEFG
1DeptAprMayJunJulAugSep
2Dept 12754591232571990
3Dept 2
4Dept 3
5Dept 4
6Dept 5
7Dept 6
8Dept 7
9Dept 8
10Dept 9
Summary
Cell Formulas
RangeFormula
B2=SUMIF(INDIRECT("'Travel Costs - "&$A2&"'!A:A"),B$1,INDIRECT("'Travel Costs - "&$A2&"'!G:G"))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Chart help - rainfall data
Replies
2
Views
261
JV0710
J
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
866
Cubist
Cubist
S
  • Question Question
Problem with x-axis labels in excel chart
Replies
1
Views
422
sharshra
S
B
  • Question Question
calculate monthly value
Replies
0
Views
237
BrutalLogiC
B
P
  • Solved
Vlookup and match issue
Replies
7
Views
73
etaf
etaf

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

Share this page

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