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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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

ADVERTISEMENT

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
48,363
Office Version
  1. 365
Platform
  1. 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 1Brakes5050
12Truck 1Maintenance00
13Truck 1Tires00
14Truck 2Brakes00
15Truck 2Maintenance5075
16Truck 2Tires100100
Summary
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,851
Members
415,862
Latest member
DemianGS

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
Top