date criteria to select range

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always greatly appreciated


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

One play ..

Assuming in Sheet1, you have real dates in B1:AF1
from say: 1-Aug-2005 to 31-Aug-2005
with the numbers (your metrics) below in B2:AF2, B3:AF3 etc

In Sheet2 (say), we could put in A2:
=SUM(OFFSET(Sheet1!A2,,MATCH(TODAY(),Sheet1!$1:$1, 0)-1,,-7))
and copy A2 down to return the desired results from the corresponding rows
in Sheet1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" wrote in
message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always greatly appreciated


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

Hi!

So, what exactly do want to sum? (where is it?)

What if today is August 6? There aren't 7 days worth of data to sum!

One way.....

Assume row 1, A1:AE1, are the date headers in the format 8/1/2005

To sum the last 7 entries in row 2 from todays date (inclusive):

=IF(COUNT(2:2)7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:H1,0)-1,,-7)))

Biff

"Kstalker" wrote in
message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always greatly appreciated


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

Ooops!

One of the cell references is wrong. Should be:

=IF(COUNT(2:2)7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:AE1,0)-1,,-7)))

Biff

"Biff" wrote in message
...
Hi!

So, what exactly do want to sum? (where is it?)

What if today is August 6? There aren't 7 days worth of data to sum!

One way.....

Assume row 1, A1:AE1, are the date headers in the format 8/1/2005

To sum the last 7 entries in row 2 from todays date (inclusive):

=IF(COUNT(2:2)7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:H1,0)-1,,-7)))

Biff

"Kstalker" wrote
in message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always greatly appreciated


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread:
http://www.excelforum.com/showthread...hreadid=395995

And if there's the possibility that the number of historic data days in
Sheet1's B1:AF1 is 7 days from "today", and you want for such situations
to just sum the available historicals up till and inclusive of "today"
(notwithstanding it's less than the specified 7 days), we could put instead
in Sheet2's A2:

=IF(MATCH(TODAY(),Sheet1!$1:$1,0)-17,SUM(OFFSET(Sheet1!A2,,MATCH(TODAY(),Sh
eet1!$1:$1,0)-1,,-(MATCH(TODAY(),Sheet1!$1:$1,0)-1))),SUM(OFFSET(Sheet1!A2,,
MATCH(TODAY(),Sheet1!$1:$1,0)-1,,-7)))

and copy down as before ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Cheers Max / Biff.

Biff as you pointed out there is the possibility of less than a weeks
worth of data if it is at the begining of the month.

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

If you have a minute, how can I acheive this?

Thanks

Kristan


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

That's what I thought you might want g.
One way .. see my follow up response in the other branch
(think you just missed it !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi!

Try this:

=IF(COUNT(2:2)7,SUM(A2:AE2),SUM(OFFSET(A2,,MATCH( TODAY(),A1:AE1,0)-1,,-7)))

Biff

"Kstalker" wrote in
message ...

Cheers Max / Biff.

Biff as you pointed out there is the possibility of less than a weeks
worth of data if it is at the begining of the month.

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

If you have a minute, how can I acheive this?

Thanks

Kristan


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

Excellent.

Thanks for that Max.

Regards

Kristan


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=395995

You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" wrote in
message ...

Excellent.

Thanks for that Max.

Regards

Kristan