"Last 7 Days" running total?

[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

 

[Don Guillett]

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.

 

[Mike H]

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

 

[Max]

One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below

 

[Mike H]

I missed your using E2007 so you can use full columns for the sumproduct
formula

 

[Bob Newman]

This formula is returning "0".

Bob

One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below

 

[Bob Newman]

This formula is returning "#VALUE!".

Bob

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

 

[Bob Newman]

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

Bob

 

[Lars-Åke Aspelin]

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

 

[Mike H]

This formula is returning "#VALUE!".

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

Mike

 

[Mike H]

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!!

 

[Don Guillett]

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

 

[Bob Newman]

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

Bob

 

[Bob Newman]

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

Bob

 

[Max]

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.

 

[Bob Newman]

They are formatted for "real" dates.

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.

 

[Max]

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.

 

[Bob Newman]

I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob

 

[Don Guillett]

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)

I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob

 

[Max]

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?