date criteria to select range

B

Biff

ugh!

<g>

Biff

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

--
Rgds
Max
xl 97
Click to expand...
 
M

Max

Kstalker said:
Mail enroute.
Unsure if it is possible.
Click to expand...

Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.

In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
The formula below is being used to sumproduct any data where the
criteria "stop" is met. However I can only sum this data (sumif) which
is not accurate as if more than one coumn contains the criteria it is
summed . Is there a way that I can set the criteria as in using the
sumif function but still produce the sumproduct results??
Click to expand...
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))

Think I might have lost my way somewhere here ..

As it stands, your posted formula is equivalent to:

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

(i.e. with the "*" replacing the SUMPRODUCT( ..))

So, supposing you have:

In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2

(All other cells within the posted formula are assumed blank)

Your posted formula will return: 4500 (nothing wrong here), viz.:

SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500

What is your expected result ?
If it's another value, pl explain how the expected result is computed

And if we clear say, cell C2, so that it becomes

In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2

Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
 
M

Max

Kristan,

Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.

Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month

In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).

*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
 
K

Kstalker

Max.

Works perfectly. An error on my behalf prevented the reply you posted a
few back from working so appologies for using up more of your time and
not using the formula correctly.

Yet again there has been a wealth of useful information on and provided
through this site, I regularly recommend it to others.

Thanks again for your input Max.

Regards

Kristan
 
K

Kstalker

No problem with all the attached solutions to this thread but have a
enhancement that needs to be made and was wondering how to acheiv
this.

Instead of running from todays date I need to run from yesterdays date
If anybody has the patience could you advise on how i reconfigure th
today component of:

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


to search on yesterdays date instead?

I am certain it is simple but cannot crack it.

Thanks in advance

Krista
 
M

Max

... to search on yesterdays date instead?

Think we just need to do an Edit > Replace on the
cell with the posted working formula

Find what: TODAY()
Replace with: TODAY()-1

which yields:

=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))

(Above lightly tested in order <g>)
 
K

Kstalker

Cheers Max.

I though it was as simple as that but I keep coming up with a 'false
result using the formula below.....

=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODAY()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1),19:19,0)+1))))

Absoultely stumped....
 
M

Max

Try instead:

=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")

Replaced : " 19:19 " in your posted formula
with: " $338:$338 "

Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>

I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
 
Top