how to get Year-to-Date sum by hlookup ???

[calimero1]

good afternoon everyone,
i hope everyone is doing well


well recently i have been trying to solve an excel issue, for which i hope i can get some help

i have a combo box where users can select the month.
on the basis of the month selection i am doing "hlookup" to return the data value as per that selection.

the problem i have is how to calculate the "rolling Year_To_Date sum" based on the combo box month selection

for example if the combo box selection is May, i need to calculate the sum from January to May; if than, selection changes to "June", i need to calculate the sum from January to June

the only limitation to the solution is that i have to use the hlookup formula to grab the data result for the month selection

i have attached an Excel file for better illustration

Than you in advance for your help

cheers,
calimero

[rylo]

Hi

Why do you need to use HLOOKUP? You can just use the result of the dropdown directly. Try

=SUM(B3:OFFSET(A3,0,P5))

rylo

[calimero1]

thank you for your reply rylo!

i have to use "hlookup`s" because i will be dealing with 40 tabs which will be "pulled" in one summary report (the attached file)

your solution is great but it wont be doable with the file and tabs i am dealing with

as per your solution i am thinking something like this:

"SUM(OFFSET(hlookup formula,0,-(P5-1)):hlookup formula)"

but when i insert that in the attached excel file i get error:
"=sum(offset(hlookup($P$6,$B$1:$M$3,3,false),0,-($P$5-1)):hlookup($P$6,$B$1:$M$3,3,false))"


not so sure what i am doing wrong

[rylo]

Hi

Can you update your example file to be more representative of your actual situation. I can't see how hlookup is necessary if all the data is being brought into one sheet.

rylo

[Trebor76]

Hi calimero,

The formula in cell B12 of the attached should do the trick. I also changed (simplified) the formula in cell B11.

HTH

Robert

[wimfrits]

Not sure if our circumstances are equal.. but I had a similar problem a while ago. On a datasheet with monthly columns (months on row 1) ranging from Jan/2004 to Dec/2010 I needed a function that would calculate a cumulative number of a year, up to the point of reference.

So if cell X1 = Oct/07
and on row 16 I'd have monthly salaries paid
then in cell X17 I'd want to calculate the total amount of salaries paid in 2007 up to and including October 2007.

I looked, but didn't find an Excel function that met my needs. (If there is, I'd like to know though!)

Solved this by the following VBA function:

Please Login or Register  to view this content.

And then Cell X17 would hold the formula =Cumulative(X16)
Note that this formula expects a row with dates on row 1.