date criteria to select range

[Kstalker]

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 heade
and then a series of metrics under each day. What I need to do is sum
days worth of historic data from and including today.

in laymans

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

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

Any help as always greatly appreciate

 

[Max]

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

 

[Biff]

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

 

[Biff]

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

 

[Max]

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)-1<7,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 ..

 

[Kstalker]

Cheers Max / Biff.

Biff as you pointed out there is the possibility of less than a week
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 ther
are less than 7 entries.

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

Thanks

Krista

 

[Max]

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

 

[Biff]

Hi!

Try this:

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

Biff

 

[Kstalker]

Excellent.

Thanks for that Max.

Regards

Krista

 

[Max]

You're welcome, Kristan !

 

[Biff]

Max, what do you see that I don't?

I would think this should be sufficient:

=IF(COUNT(2:2)<7,SUM(A2:AE2),.................

Biff

 

[Max]

Max, what do you see that I don't?
I would think this should be sufficient:
=IF(COUNT(2:2)<7,SUM(A2:AE2),.................

Trouble is <g>, I had assumed that there could be previous month's metrics
still residing within the data rows (to the right of the current date's
col), either missed* out in the monthly purging exercise despite the refresh
of the current month's dates in the header row
*or yet to be purged because the data entry method followed is to
progressively overwrite.

 

[Kstalker]

Works well thanks Biff and Max.

I have one other question relating to this... I am trying t
incorporate a sumproduct function into the formula and cannot quite ge
it to behave how I would like.

=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1
0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1
0)-1,,-7)))/T25))-1,,-(MATCH(TODAY(),$1:$1,0)-1))),SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1
0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1, 0)-1,,-7)))/T25)


Again, if you get a minute could you take a look at the attache
formula and poitn out the error in my ways.

Thanks again.
Krista

 

[Max]

Try instead :

=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))


--

 

[Max]

Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)

=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))

 

[Kstalker]

Excellent.

It works a treat.

I have another question relating to this entire process.

The formula below is being used to sumproduct any data where th
criteria "stop" is met. However I can only sum this data (sumif) whic
is not accurate as if more than one coumn contains the criteria it i
summed . Is there a way that I can set the criteria as in using th
sumif function but still produce the sumproduct results??


=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30))

Thanks again


Thank

 

[Kstalker]

Alternatively.... Is it possible to add criteria to the equation below.

The reason I ask is I am trying to stop the equation below (which work
perfectly thanks Max) from going past the 1st of the month should i b
trying to add a weekly summary from the 6th day of the month back t
the 1st.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 .......etc
(--------------)

"=IF(S17=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A17,,MATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))"

All help greatly appreciate

 

[Max]

Believe essentially that you have dates running along from B1 across (B1,
C1, D1, ...) which do not necessarily start from the 1st of the month in B1

Try this revision:

=IF(S17=0,0,IF(AND(DAY(TODAY())>=1,DAY(TODAY())<=6),SUMPRODUCT((OFFSET(A9,,M
ATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),M
ONTH(TODAY()),1),$1:$1,0)+1))),(OFFSET(A17,,MATCH(TODAY(),$1:$1,0)-1,,-(MATC
H(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0)+1))))
/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATC
H(TODAY(),$1:$1,0)-1,,-7)))/S17))

The changes made are to the 2nd IF where:

IF(AND(DAY(TODAY())>=1,DAY(TODAY())<=6)

replaces the previous :

IF(MATCH(TODAY(),$1:$1,0)-1<7

and to the width params of OFFSET within the 1st SUMPRODUCT, where:

-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0)
+1

replaces the previous:

-(MATCH(TODAY(),$1:$1,0)-1

 

[Kstalker]

Thanks again Max.

Just about there..... My date range starts at the 1st and then through
to however many days of the month. However I need to offer a week to
date summary of the metrics. If the week to date happens to fall on the
6th, 5th, 4th, 3rd , 2nd or 1st then the formula will run over into the
columns to the left of the data. ( i am running left to right 1st ==>
31st.

So what I need to do is stop the formula when there is no date in the
reference date. Unfortunately i have various other calcs running in
these spaces that are numeric which will throw the weighted
averages.......Is this possible?

Regards

Kristan

 

[Max]

.......... Is this possible?

Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..