May 01 2021 11:26 AM - edited May 01 2021 11:29 AM
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.
May 01 2021 11:32 AM - edited May 01 2021 11:37 AM
SolutionYou 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.
May 01 2021 11:40 AM
May 01 2021 09:10 PM - edited May 01 2021 09:11 PM
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...
May 02 2021 10:56 AM
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?
May 02 2021 12:35 PM
@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.
May 01 2021 11:32 AM - edited May 01 2021 11:37 AM
SolutionYou 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.