SOLVED

Summing - Vlookup and Dates

Steel Contributor

Hello Experts,

 

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

 

I think it would be some sort of combination of Vlookup and functionality added for the date of the amendment (that is the hard part).  Basically, I have an initial amount of $10 for companyA and this amount is expected to increase by $XX on a certain date and decrease by $XX on a certain date.  My table should populate based on these expected increases/decreases.  It is probably much easier to see the file than to explain. 

 

Kindly see attached file.  Let me know if you have any questions.  grateful for your help. 

Tony2021_1-1619893720324.png

 

 

6 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

You need to try this:

=$G2 + Sumifs($C$2:$C$4,  $B$2:$B$4, "<="&H$1,  $A$2:$A$4, $F2)

I have attached the file with the solution. Hope it addresses your need.

Viz, wow that is amazing! Thank you so much for the quick response and perfect formula. It gives me exactly what I need. I will need to incorporate into my production file. I dont forsee any issues. Thank you!
You are welcome

@Viz 

Hi Viz,

I might have an issues.  Sorry.  Appreciate when you have a sec.  When I add additional dates to the data set for some reason the sum doesnt appear to be correct anylonger but only if I add dates.
If could kindly check out the attached I would greatly appreciate.

thank you...

@Tony2021 

 

Hi Tony, actually what is happening that for BOA you have positive entry 1000 on 10-May and negative entry of 3000 on 01-June. The formula I gave takes both the amendments while giving the applicable price for 01-June (so, 1-June price 1000 + 1000 - 3000 = -1000)

 

In your manual calculation, you have ignored the 10-May amendment. Is it that you want only the latest amendment to be considered if there are multiple amendments between two dates?

@Viz sorry I found out my problem.  It might be difficult to explain but in my production file where all of the data resides the date was formated as MMM - yy and there was not a specific day while in the file I posted there was a specific day and I didnt catch that there was a difference in date formats.  Sorry for any confusion .  This drove me nuts.  

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

You need to try this:

=$G2 + Sumifs($C$2:$C$4,  $B$2:$B$4, "<="&H$1,  $A$2:$A$4, $F2)

I have attached the file with the solution. Hope it addresses your need.

View solution in original post