Sumproduct with Multiple Rows and Columns

[jdavid1006]

Hi guys,

Although I've been reading for a number of years, this is my first post. Please bear with me if I do something incorrectly.

I have a workbook that is basically taking invoices and rolling them up by date, truck number, and expense type. The problem is there can be multiple invoices in a month. I think I need to use the sumproduct formula but I'm not super familiar with it.

I have a summary that looks like the table below but I need to see everything by month and YTD. I can still have the Truck and Type columns (brakes, tires, etc...). Any ideas on what I can do? Thank you!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Invoice 123[/TD]
[TD]Invoice 456[/TD]
[TD]Invoice 789[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]August[/TD]
[TD]August[/TD]
[TD]September[/TD]
[/TR]
[TR]
[TD]Truck 1[/TD]
[TD]Brakes[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 2[/TD]
[TD]Tires[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 2[/TD]
[TD]Maintenance[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Truck 3[/TD]
[TD]Brakes[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Truck 4[/TD]
[TD]Maintenance[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

What is you expected result ... ???

 

[jdavid1006]

I need my final result to look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]August[/TD]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 1[/TD]
[TD]Brakes[/TD]
[TD]50[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 1[/TD]
[TD]Maintenance[/TD]
[TD]25[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 1[/TD]
[TD]Tires[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 2[/TD]
[TD]Brakes[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 2[/TD]
[TD]Maintenance[/TD]
[TD]25[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck 2[/TD]
[TD]Tires[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And so on...

 

[James006]

Have you tried to Insert a Pivot Table ...?

 

[jdavid1006]

Good idea but I need to compare it to a budget file. The pivot table can be referenced but it's not very pretty and this will be seen by senior leadership. I'd like to use formulas, if I can.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this helps. Each formula copied down.

Excel Workbook
	A	B	C	D	E
1			Invoice 123	Invoice 456	Invoice 789
2			August	August	September
3	Truck 1	Brakes	50
4	Truck 2	Tires		100
5	Truck 2	Maintenance	25	25	25
6	Truck 3	Brakes			50
7	Truck 4	Maintenance		25	25
8
9
10			August	YTD
11	Truck 1	Brakes	50	50
12	Truck 1	Maintenance	0	0
13	Truck 1	Tires	0	0
14	Truck 2	Brakes	0	0
15	Truck 2	Maintenance	50	75
16	Truck 2	Tires	100	100
Summary

 

[jdavid1006]

That works. Thank you, Peter!

 

[Peter_SSs]

That works. Thank you, Peter!

You're welcome. Thanks for the follow-up.