Sumproduct with Multiple Rows and Columns

jdavid1006

New Member
Joined
Oct 21, 2019
Messages
6
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!

Invoice 123Invoice 456Invoice 789
AugustAugustSeptember
Truck 1Brakes50
Truck 2Tires100
Truck 2Maintenance252525
Truck 3Brakes50
Truck 4Maintenance2525

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jdavid1006

New Member
Joined
Oct 21, 2019
Messages
6
I need my final result to look like this:


AugustYTD
Truck 1Brakes50150
Truck 1Maintenance25200
Truck 1Tires100100
Truck 2Brakes0200
Truck 2Maintenance25200
Truck 2Tires0200

<tbody>
</tbody>


And so on...
 

jdavid1006

New Member
Joined
Oct 21, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,611
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

See if this helps. Each formula copied down.

Excel Workbook
ABCDE
1Invoice 123Invoice 456Invoice 789
2AugustAugustSeptember
3Truck 1Brakes50
4Truck 2Tires100
5Truck 2Maintenance252525
6Truck 3Brakes50
7Truck 4Maintenance2525
8
9
10AugustYTD
11Truck 1Brakes
50
50
12Truck 1Maintenance00
13Truck 1Tires00
14Truck 2Brakes00
15Truck 2Maintenance5075
16Truck 2Tires100100
Summary
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,113
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top