Forum Discussion
Solved
Sum function and v/hlookup
Hi guys! I'm currently in an internship and I need to update a file. Basically what I need is the possibility to make the sum from sum(M6:Q6) into a changing sum depending on the starting point (...
Virginie, please see it in attached file (cell in green)
Hi Virginie,
Use OFFSET, like
=SUM(OFFSET($F$4,$B$1-1,B10,1,12-B10))
where instead of B10 it will be VLOOKUP for it's calculation. I prefer INDEX/MATCH, but that doesn't matter
=SUM(
OFFSET($F$4,
$B$1-1,
INDEX($A$4:$C$15,MATCH($A$1,$A$4:$A$15,0),2),
1,
12-INDEX($A$4:$C$15,MATCH($A$1,$A$4:$A$15,0),2)
)
)
Hi Sergei,
Thanks for your response. Unfortunately, the formula seems to not be working, it gives me a result of 20 instead of 12.
Would you know why it happened?
Virginie, please see it in attached file (cell in green)
Thank you so much!