sum number of cells based on current date in excel spreadsheet

Question

I've got a spreadsheet like this:

date | 7/1 | 7/2 | 7/3 | 7/4
-----|-----|-----|-----|-----
 val |  3  |  5  |  1  |  3  
-----|-----|-----|-----|-----

I want to sum the val row, but only up to the current date. So if today were 7/3, the sum would be 3+5+1=9. If today were 7/4, it would be 12.

I figured this out to get the number of columns:

=YEARFRAC(B1,TODAY())*360  // B1 is the first date -- 7/1

but I can't figure out how to tell excel to do the sum:

=SUM(B2:<B+num cols above>2)

Presumably its something to do with references, and lookup, but I'm not really familiar with how those work....

Answer 1

The OFFSET function should do the job. There's a similar question here. Without knowing the exact layout of your table, I guess your formula would look something like:

=SUM(OFFSET(B2,0,0,1,DAY(TODAY())))

Where DAY(TODAY()) returns the day in the month. This number is then used as the width of the range to SUM over in OFFSET.

I hope that was relatively clear. Good luck.

Answer 2

You can use SUMIF:

=SUMIF(A1:E1,"<="&TODAY(),A2:E2)

Assuming your dates are in a1:e1 and your values are in a2:e2.

Answer 3

Put this VBA in a module

Option Explicit

Public Function GetTotal(StartCell As Range) As Integer 
    Dim i As Integer, j As Integer

    i = StartCell.Row
    j = StartCell.Column

    If Cells(i, j) > Date Then
        GetTotal = 0
        Exit Function
    End If

    While Cells(i, j) <> DateAdd("d", 1, Date) 'values up to an including today'
        GetTotal = GetTotal + Cells(i + 1, j)
        j = j + 1
    Wend

End Function

and then use it in a worksheet cell by inserting

=GetTotal([starting date cell])

where [starting date cell] is the cell with the date from which you want the sum. The worksheet cell's value will be the sum

Answer 4

Second on the sumif. Defining your own vba function is an option but I usually reserve something like that for when there's no other options.