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

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

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

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

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

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

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:

Function Cumulative(DataItem)

Dim n1 As Integer
Dim ws_cumulative As String
Dim nResult  As Double

    ws_cumulative = DataItem.Worksheet.name
    nResult = 0

    For n1 = DataItem.column - Month(Sheets(ws_cumulative).Cells(1, DataItem.column).Value) + 1 To DataItem.column
        If Not IsError(Sheets(ws_cumulative).Cells(DataItem.row, n1).Value) Then
            If IsNumeric(Sheets(ws_cumulative).Cells(DataItem.row, n1).Value) Then
                If Not Sheets(ws_cumulative).Cells(DataItem.row, n1).Value = "" Then
                    nResult = nResult + Sheets(ws_cumulative).Cells(DataItem.row, n1).Value
                End If
            End If
        End If
    Next n1

    Cumulative = nResult

End Function

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