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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Newman" wrote in message
...
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

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" wrote:

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

.

One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
--
Max
Singapore
---
"Bob Newman" wrote:
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.

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

"Mike H" wrote:

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" wrote:

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

.

This formula is returning "0".

Bob

"Max" wrote in message
...
One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
--
Max
Singapore
---
"Bob Newman" wrote:
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.

This formula is returning "#VALUE!".

Bob

"Mike H" wrote in message
...
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" wrote:

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

.

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

Bob

"Don Guillett" wrote in message
...
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" wrote in message
...
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

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


On Sat, 19 Dec 2009 13:33:27 -0500, "Bob Newman"
wrote:

This formula is returning "0".

Bob

"Max" wrote in message
...
One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
--
Max
Singapore
---
"Bob Newman" wrote:
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.

This formula is returning "#VALUE!".

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

Mike

"Bob Newman" wrote:

This formula is returning "#VALUE!".

Bob

"Mike H" wrote in message
...
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" wrote:

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

"Bob Newman" wrote:

This formula is returning "0".

Bob

"Max" wrote in message
...
One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
--
Max
Singapore
---
"Bob Newman" wrote:
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.

.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Newman" wrote in message
...
Is this an auto-reply? It looks like it. The question is pretty straight
forward.

Bob

"Don Guillett" wrote in message
...
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" wrote in message
...
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

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

Bob

"Mike H" wrote in message
...
This formula is returning "#VALUE!".

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

Mike

"Bob Newman" wrote:

This formula is returning "#VALUE!".

Bob

"Mike H" wrote in message
...
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" wrote:

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

.

.

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

Bob

"Mike H" wrote in message
...
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!!

"Bob Newman" wrote:

This formula is returning "0".

Bob

"Max" wrote in message
...
One idea ...
=SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7))
Success? hit the YES below
--
Max
Singapore
---
"Bob Newman" wrote:
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.

.

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 FormatCells.
Real dates are numbers, increasing chronologically. Hence the latest date =
maximum number in col A.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
Again: When I use the sum function it totals the cells okay. Doesn't that
mean they are numbers?

They are formatted for "real" dates.

Bob

"Max" wrote in message
...
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
FormatCells. Real dates are numbers, increasing chronologically. Hence
the latest date = maximum number in col A.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
Again: When I use the sum function it totals the cells okay. Doesn't
that mean they are numbers?

Do your dates in col A respond properly when you try formatting it to
another date format via FormatCellsDate (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 FormatCellsDate. Recheck, let me know the results
here.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
They are formatted for "real" dates.

Bob

"Max" wrote in message
...
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 FormatCells. Real dates are numbers, increasing chronologically.
Hence the latest date = maximum number in col A.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
Again: When I use the sum function it totals the cells okay. Doesn't
that mean they are numbers?

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

Bob

"Max" wrote in message
...
Do your dates in col A respond properly when you try formatting it to
another date format via FormatCellsDate (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 FormatCellsDate. Recheck, let me know the
results here.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
They are formatted for "real" dates.

Bob

"Max" wrote in message
...
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 FormatCells. Real dates are numbers, increasing
chronologically. Hence the latest date = maximum number in col A.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
Again: When I use the sum function it totals the cells okay. Doesn't
that mean they are numbers?

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" wrote in message
...
I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob

"Max" wrote in message
...
Do your dates in col A respond properly when you try formatting it to
another date format via FormatCellsDate (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 FormatCellsDate. Recheck, let me know
the results here.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
They are formatted for "real" dates.

Bob

"Max" wrote in message
...
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 FormatCells. Real dates are numbers,
increasing chronologically. Hence the latest date = maximum number in
col A.
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
Again: When I use the sum function it totals the cells okay. Doesn't
that mean they are numbers?

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?
--
Max
Singapore
-----
"Bob Newman" wrote in message
...
I surrender. This is more difficult than I anticipated. I'll do it
manually.

Bob