SOLVED

# Summing - Vlookup and Dates

Occasional Contributor

# Summing - Vlookup and Dates

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.

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

# Re: Summing - Vlookup and Dates

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.

# Re: Summing - Vlookup and Dates

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

# Re: Summing - Vlookup and Dates

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...

# Re: Summing - Vlookup and Dates

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?

# Re: Summing - Vlookup and 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.