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 (ie that it automatically starts at the right columm so that I don't need to do it manually every month).
I've tried an hlookup and it did give me the right columm to start with but I can't seem to make it go to FU6 ( meaning that I would have to manually add the right colums afterwards).
I'm not sure if I'm being very clear but the problem is quite complicated. The formula I've come up with this far is the folowing:
SUM(HLOOKUP(VLOOKUP(A1;$A$4:$C$15;3;FALSE);$F$4:$Q$28;B1;FALSE);Q6)
Basically I was wondering if there was a way to make it work with a synthax similar to SUM(XXX:Q6) instead of SUM(XXX;Q6) with the formula I used.
PS: the file I've attached is not the one I'm working on but it will give you the kind of information I work with so you can better visualise the problem. I've highlighted in yellow the cell with the formula.
Thank you for your help!
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)
