Excel Formula Needed

[tim220225]

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

 

[alansidman]

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.

 

[tim220225]

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.

Thanks for the reply. I would post the workbook but I am not allowed to which makes this difficult. Any suggestions?

 

[alansidman]

Yup. Take a couple of pictures and post them. Insert Image. Keep the amount of data to a minimum.

 

[tim220225]

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

Loaded Miles	Deadhead Miles	Pick-Up Date	Delivery Date	Year	Month
228	122	8/14/2018	8/14/2018	2018	Aug-18
254	218	8/15/2018	8/15/2018	2018	Aug-18
49	55	9/11/2018	9/11/2018	2018	Sep-18
0	253	9/17/2018	9/17/2018	2018	Sep-18
84	220	10/2/2018	10/2/2018	2018	Oct-18
203	205	10/8/2018	10/9/2018	2018	Oct-18
10	19	10/12/2018	10/12/2018	2018	Oct-18

<tbody>
</tbody>

A B C D E F G H I J K L M N O

Year

Mileage Type

Jan-18

Feb-18

Mar-18

Apr-18

May-18

Jun-18

Jul-18

Aug-18

Sep-18

Oct-18

Nov-18

Dec-18

YTD

2018

Loaded Miles

0

Deadhead Miles

Total

<tbody>
</tbody>

 

[AhoyNC]

Here's one way.
Copy formulas across columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Year	Mileage Type	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	YTD
2	2018	Loaded Miles	0	0	0	0	0	0	0	482	49	297	0	0	828
3		Deadhead Miles	0	0	0	0	0	0	0	340	308	444	0	0	1092
4		Total	0	0	0	0	0	0	0	822	357	741	0	0	1920
Sheet2

Excel Workbook
	A	B	C	D	E	F
1	Loaded Miles	Deadhead Miles	Pick-Up Date	Delivery Date	Year	Month
2	228	122	8/14/2018	8/14/2018	2018	18-Aug
3	254	218	8/15/2018	8/15/2018	2018	18-Aug
4	49	55	9/11/2018	9/11/2018	2018	18-Sep
5	0	253	9/17/2018	9/17/2018	2018	18-Sep
6	84	220	10/2/2018	10/2/2018	2018	18-Oct
7	203	205	10/8/2018	10/9/2018	2018	18-Oct
8	10	19	10/12/2018	10/12/2018	2018	18-Oct
Sheet1

 

[tim220225]

Here's one way.
Copy formulas across columns.

Sheet2

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Year	Mileage Type	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	YTD
2	2018	Loaded Miles	0	0	0	0	0	0	0	482	49	297	0	0	828
3		Deadhead Miles	0	0	0	0	0	0	0	340	308	444	0	0	1092
4		Total	0	0	0	0	0	0	0	822	357	741	0	0	1920

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

	A	B	C	D	E	F
1	Loaded Miles	Deadhead Miles	Pick-Up Date	Delivery Date	Year	Month
2	228	122	8/14/2018	8/14/2018	2018	18-Aug
3	254	218	8/15/2018	8/15/2018	2018	18-Aug
4	49	55	9/11/2018	9/11/2018	2018	18-Sep
5	0	253	9/17/2018	9/17/2018	2018	18-Sep
6	84	220	10/2/2018	10/2/2018	2018	18-Oct
7	203	205	10/8/2018	10/9/2018	2018	18-Oct
8	10	19	10/12/2018	10/12/2018	2018	18-Oct

<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>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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?

 

[AhoyNC]

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.

 

[AhoyNC]

I tried changing the dates and the formula works for me.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Year	Mileage Type	17-Jan	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	YTD
2	2017	Loaded Miles	0	0	0	0	0	0	0	0	0	0	100	300	400
3		Deadhead Miles	0	0	0	0	0	0	0	0	0	0	50	75	125
4		Total	0	0	0	0	0	0	0	0	0	0	150	375	525
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Year	Mileage Type	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	YTD
2	2018	Loaded Miles	0	0	0	0	0	0	0	482	49	297	0	0	828
3		Deadhead Miles	0	0	0	0	0	0	0	340	308	444	0	0	1092
4		Total	0	0	0	0	0	0	0	822	357	741	0	0	1920
Sheet2

Excel Workbook
	A	B	C	D	E	F
1	Loaded Miles	Deadhead Miles	Pick-Up Date	Delivery Date	Year	Month
2	228	122	8/14/2018	8/14/2018	2018	18-Aug
3	254	218	8/15/2018	8/15/2018	2018	18-Aug
4	49	55	9/11/2018	9/11/2018	2018	18-Sep
5	0	253	9/17/2018	9/17/2018	2018	18-Sep
6	84	220	10/2/2018	10/2/2018	2018	18-Oct
7	203	205	10/8/2018	10/9/2018	2018	18-Oct
8	10	19	10/12/2018	10/12/2018	2018	18-Oct
9	100	50	11/12/2017	11/12/2017	2017	12-Nov
10	300	75	12/2/2017	12/4/2017	2017	4-Dec
Sheet1