# "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.

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

M

#### Mike H

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

B

#### Bob Newman

This formula is returning "0".

Bob

Max said:
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

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.

B

#### Bob Newman

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

Bob

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