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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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...
 
Upvote 0
Have you tried to Insert a Pivot Table ...?
 
Upvote 0
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.
 
Upvote 0
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 1Brakes5050
12Truck 1Maintenance00
13Truck 1Tires00
14Truck 2Brakes00
15Truck 2Maintenance5075
16Truck 2Tires100100
Summary
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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