"Last 7 Days" running total?

  • Thread starter Thread starter Bob Newman
  • Start date Start date
B

Bob Newman

Excel 2007
I have a list containing daily sales figures that is updated each day. I
would like to have a formulas that will total sales figures (column C) for
the last 7 days (column A). Figures are being added daily so the "last 7
days" will be constantly changing. Suggestions please.

Thanks in advance... Bob
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
Bob,

Your question isn't clear but if you want to sum the last 7 entries in
column C try this

=SUM(OFFSET(C1,COUNTA(C:C)-7,):OFFSET(C1,COUNTA(C:C),))

or to sum any date in the last 7 days

=SUMPRODUCT((A1:A5000>=TODAY()-6)*(C1:C5000))

Mike
 
One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
 
This formula is returning "#VALUE!".

Bob

Mike H said:
Bob,

Your question isn't clear but if you want to sum the last 7 entries in
column C try this

=SUM(OFFSET(C1,COUNTA(C:C)-7,):OFFSET(C1,COUNTA(C:C),))

or to sum any date in the last 7 days

=SUMPRODUCT((A1:A5000>=TODAY()-6)*(C1:C5000))

Mike
Click to expand...
 
Is this an auto-reply? It looks like it. The question is pretty straight
forward.

Bob
 
The formula works fine for me.
Make sure that you don't have any data in column A below the table of
dates. Also make sure that you don't have any dates in column A that
don't (yet) have any corresponding sales figures in column C.
If either if these conditions can't be fulfilled, please give further
information about the layout of your worksheet.

Hope this helps / Lars-Åke
 
This formula is returning "#VALUE!".

Then your numbers aren't numbers, you have text in the range

Mike
 
If Max will excuse me commenting on his formula the reason it returns zero is
the same reason my formula returns VALUE, your numbers aren't numbers!!
 
No. It's because I have no idea of your layout. Often, when a solution is
presented a poster will come back and say "it doesn't work". This macro
should fix your numbers that aren't numbers.

Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each c In Selection 'Range("a1:q" & lr)
If Trim(Len(c)) > 0 And c.HasFormula = False Then
c.NumberFormat = "General"
c.Value = CDbl(c)
End If
Next

Application.ScreenUpdating = True
End Sub
 
When I use the sum function it totals the cells okay. Doesn't that mean
they are numbers?

Bob
 
Again: When I use the sum function it totals the cells okay. Doesn't that
mean
they are numbers?

Bob
 
Check your dates in col A. These should be real dates recognized by Excel,
not text dates. You can easily convert all text dates in col A at one go to
real dates using Data > Text to Cols (with col A selected). In step 3 of the
wiz, under "Column data format", check "Date", then select the format of the
text dates from the droplist, eg: DMY. Click to Finish. Real dates will
respond when you say, format it to another date format via Format>Cells.
Real dates are numbers, increasing chronologically. Hence the latest date =
maximum number in col A.
 
They are formatted for "real" dates.

Bob

Max said:
Check your dates in col A. These should be real dates recognized by Excel,
not text dates. You can easily convert all text dates in col A at one go
to real dates using Data > Text to Cols (with col A selected). In step 3
of the wiz, under "Column data format", check "Date", then select the
format of the text dates from the droplist, eg: DMY. Click to Finish. Real
dates will respond when you say, format it to another date format via
Format>Cells. Real dates are numbers, increasing chronologically. Hence
the latest date = maximum number in col A.
Click to expand...
 
Do your dates in col A respond properly when you try formatting it to
another date format via Format>Cells>Date (choose another date type) ? This
is one easy way to test it. Text dates will NOT respond to any kind of date
formatting applied via Format>Cells>Date. Recheck, let me know the results
here.
 
I repeat my offer to send your file
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bob Newman said:
I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob
Click to expand...
 
But why? The formula should work ok for YOU as well
Why don't you just copy n paste your actual last 7 days dates data here?
 
Back
Top