"Last 7 Days" running total?

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
 
D

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.
 
M

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
 
M

Max

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

Bob Newman

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
 
B

Bob Newman

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

Bob
 
L

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
 
M

Mike H

This formula is returning "#VALUE!".

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

Mike
 
M

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

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
 
B

Bob Newman

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

Bob
 
B

Bob Newman

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

Bob
 
M

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.
 
B

Bob Newman

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.
 
M

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.
 
D

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)
Bob Newman said:
I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob
 
M

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top