Excel Formula Needed

T

tim220225

New Member
Joined
Jun 4, 2012
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I seem to be stumped trying to assemble a formula. I need to total the mileage run by month from one worksheet to another summary worksheet by the month. Basically I have column A called loaded miles driven and in column B I have deadhead miles driven. Column C has the month and year formatted as Aug-2018 for example. these totals need to be drawn to a second page that looks like a calendar. I would appreciate all suggestions.

Tim
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Difficult to visualize what you want. Suggest you show us a couple of lines of data and then what you want Sheet2 to look like for the data provided.
 
Upvote 0
alansidman said:
Difficult to visualize what you want. Suggest you show us a couple of lines of data and then what you want Sheet2 to look like for the data provided.
Click to expand...

Thanks for the reply. I would post the workbook but I am not allowed to which makes this difficult. Any suggestions?
 
Upvote 0
Yup. Take a couple of pictures and post them. Insert Image. Keep the amount of data to a minimum.
 
Upvote 0
How does this look? Column A would be Loaded Miles in the first data set and Year in the second set. What I need is for the formula to total the Loaded mileage in column A to the Loaded Mileage row in the second set. Hope this makes sense.



A B C D E F

[TABLE="width: 574"]
<tbody>[TR]
[TD]Loaded Miles[/TD]
[TD]Deadhead Miles[/TD]
[TD]Pick-Up Date[/TD]
[TD]Delivery Date[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]228[/TD]
[TD]122[/TD]
[TD]8/14/2018[/TD]
[TD]8/14/2018[/TD]
[TD]2018[/TD]
[TD]Aug-18[/TD]
[/TR]
[TR]
[TD]254[/TD]
[TD]218[/TD]
[TD]8/15/2018[/TD]
[TD]8/15/2018[/TD]
[TD]2018[/TD]
[TD]Aug-18[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]55[/TD]
[TD]9/11/2018[/TD]
[TD]9/11/2018[/TD]
[TD]2018[/TD]
[TD]Sep-18[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]253[/TD]
[TD]9/17/2018[/TD]
[TD]9/17/2018[/TD]
[TD]2018[/TD]
[TD]Sep-18[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]220[/TD]
[TD]10/2/2018[/TD]
[TD]10/2/2018[/TD]
[TD]2018[/TD]
[TD]Oct-18[/TD]
[/TR]
[TR]
[TD]203[/TD]
[TD]205[/TD]
[TD]10/8/2018[/TD]
[TD]10/9/2018[/TD]
[TD]2018[/TD]
[TD]Oct-18[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]19[/TD]
[TD]10/12/2018[/TD]
[TD]10/12/2018[/TD]
[TD]2018[/TD]
[TD]Oct-18[/TD]
[/TR]
</tbody>[/TABLE]



A B C D E F G H I J K L M N O
[TABLE="width: 1004"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Mileage Type[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Loaded Miles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deadhead Miles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Here's one way.
Copy formulas across columns.

Excel Workbook
ABCDEFGHIJKLMNO
1YearMileage Type18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-DecYTD
22018Loaded Miles00000004824929700828
3Deadhead Miles0000000340308444001092
4Total0000000822357741001920
Sheet2
Excel Workbook
ABCDEF
1Loaded MilesDeadhead MilesPick-Up DateDelivery DateYearMonth
22281228/14/20188/14/2018201818-Aug
32542188/15/20188/15/2018201818-Aug
449559/11/20189/11/2018201818-Sep
502539/17/20189/17/2018201818-Sep
68422010/2/201810/2/2018201818-Oct
720320510/8/201810/9/2018201818-Oct
8101910/12/201810/12/2018201818-Oct
Sheet1
 
Upvote 0
AhoyNC said:
Here's one way.
Copy formulas across columns.

Sheet2

ABCDEFGHIJKLMNO

<colgroup><col style="width:30px; "><col style="width:80px;"><col style="width:121px;"><col style="width:79px;"><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Mileage Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Mar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Apr[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Jun[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Oct[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Nov[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]18-Dec[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]YTD[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Loaded Miles[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]482[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]297[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]828[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Deadhead Miles[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]340[/TD]
[TD="align: center"]308[/TD]
[TD="align: center"]444[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1092[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Total[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]822[/TD]
[TD="align: center"]357[/TD]
[TD="align: center"]741[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1920[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=SUMPRODUCT(--(MONTH(C$1)=MONTH(Sheet1!$F$2:$F$8)),--(YEAR(C$1)=YEAR(Sheet1!$F$2:$F$8)),Sheet1!$A$2:$A$8)
C3=SUMPRODUCT(--(MONTH(C$1)=MONTH(Sheet1!$F$2:$F$8)),--(YEAR(C$1)=YEAR(Sheet1!$F$2:$F$8)),Sheet1!$B$2:$B$8)

<tbody>
</tbody>

<tbody>
</tbody>


Sheet1

ABCDEF

<colgroup><col style="width:30px; "><col style="width:97px;"><col style="width:116px;"><col style="width:104px;"><col style="width:108px;"><col style="width:77px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Loaded Miles[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Deadhead Miles[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Pick-Up Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Delivery Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Month[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]228[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Aug[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Aug[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]9/11/2018[/TD]
[TD="align: right"]9/11/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Sep[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]253[/TD]
[TD="align: right"]9/17/2018[/TD]
[TD="align: right"]9/17/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Sep[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Oct[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]10/8/2018[/TD]
[TD="align: right"]10/9/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Oct[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]10/12/2018[/TD]
[TD="align: right"]10/12/2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]18-Oct[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

This has worked great so far, thank you! I forgot to mention that the year 2018 on Sheet 2 cell A2 is fluid. When I change the year then cell C1 on sheet 2 will update to the month and year, 17-Jan for example. This allows me to review an entire year at a glance and compare the data to a different year. This is needed because the mileage and dates on sheet 1 includes multiple years. How can the formula be changed to accommodate this?
 
Upvote 0
Formula should still work. It's pulling the year and month that shows in cells C1 to N1. If the year changes to 2017 then the formula will look for 2017 on sheet1.
 
Upvote 0
I tried changing the dates and the formula works for me.

Excel Workbook
ABCDEFGHIJKLMNO
1YearMileage Type17-Jan17-Feb17-Mar17-Apr17-May17-Jun17-Jul17-Aug17-Sep17-Oct17-Nov17-DecYTD
22017Loaded Miles0000000000100300400
3Deadhead Miles00000000005075125
4Total0000000000150375525
Sheet2


Excel Workbook
ABCDEFGHIJKLMNO
1YearMileage Type18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-DecYTD
22018Loaded Miles00000004824929700828
3Deadhead Miles0000000340308444001092
4Total0000000822357741001920
Sheet2
Excel Workbook
ABCDEF
1Loaded MilesDeadhead MilesPick-Up DateDelivery DateYearMonth
22281228/14/20188/14/2018201818-Aug
32542188/15/20188/15/2018201818-Aug
449559/11/20189/11/2018201818-Sep
502539/17/20189/17/2018201818-Sep
68422010/2/201810/2/2018201818-Oct
720320510/8/201810/9/2018201818-Oct
8101910/12/201810/12/2018201818-Oct
91005011/12/201711/12/2017201712-Nov
103007512/2/201712/4/201720174-Dec
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Formula Help - Time it takes to run 100 miles and then convert it to time of day
Replies
7
Views
549
Hankmardukaas
H
C
  • Solved
Return a Prior Month's Value from within the Same Table?
2 3
Replies
22
Views
440
Peter_SSs
Peter_SSs
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
929
Insurance Dave
I
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
M
  • Question Question
Need a formula to show first month of tracked savings
Replies
3
Views
107
Phuoc
P

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

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