Sum based on Dates in a Matrix table

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hello Experts,

I need a formula that will sum values based on a the dates of expected monthly dollar increases or decreases contained in a table for a matrix of companies.

I have the following formula but it fails when there are many dates and only works if there are only a few dates.
=$G2+SUMIFS($C$2:$C$9,$B$2:$B$9,"<="&H$1,$A$2:$A$9,$F2)

Basically, I have an initial amount of $XX for example for companyA and this amount is expected to increase by $XX on a certain date and decrease by $XX on a certain date according to the table on the left. My table should populate based on these expected increases/decreases. It is probably much easier to see the file than to explain (but I cant upload a file I believe. The Xl2BB doesnt work on this computer either. I hope the below appears correctly.

Initial Amount:
Date FromAmendment
4/1/2021​
5/1/2021​
6/1/2021​
7/1/2021​
8/1/2021​
9/1/2021​
10/1/2021​
11/1/2021​
12/1/2021​
Bank of America NA
5/10/2021​
$1,000​
Bank of America NA$ 1,000$ 1,000
-1000​
1000​
1000​
1000​
1000​
2000​
1000​
Bank of America NA
6/1/2021​
($3,000)​
General Electric$ 8,000
6000​
6000​
6000​
6000​
6000​
6000​
9000​
9000​
Bank of America NA
7/1/2021​
$2,000​
BOEM$ 2,000
2000​
2000​
2000​
4000​
4000​
4000​
4000​
4000​
Bank of America NA
10/3/2021​
$1,000​
*Bank of America is not correct but the others are
Bank of America NA
11/21/2021​
($1,000)​
General Electric
5/1/2021​
($2,000)​
Manually input (this is what I need):
General Electric
11/1/2021​
$3,000​
4/1/2021​
5/1/2021​
6/1/2021​
7/1/2021​
8/1/2021​
9/1/2021​
10/1/2021​
11/1/2021​
12/1/2021​
BOEM
8/1/2021​
$2,000​
Bank of America NA$ 1,000
1000​
-2000​
0​
0​
0​
0​
1000​
-1000​
General Electric$ 8,000
6000​
6000​
6000​
6000​
6000​
6000​
9000​
9000​
BOEM$ 2,000
2000​
2000​
2000​
0​
0​
0​
0​
0​
I added dates w amounts to bank of america and it buggers up the summing.
 

Attachments

  • Capture.JPG
    Capture.JPG
    233.4 KB · Views: 11

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
My sum for Bank of America was not correct actually. I noticed an error in my math. The original formula might work but if someone could confirm I would be greatful or maybe another approach.
 
Upvote 0
Sorry never mind. The problem I had is that my production file and the dumby data posted in this post had different date formats and was throwing off the summing. Example: in production file the dates were formatted as May - 21 and in the pasted data above its a specific date.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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