K
Kstalker
Mail enroute.
Unsure if it is possible.
Thanks
Kristan
Unsure if it is possible.
Thanks
Kristan
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
Kstalker said:Mail enroute.
Unsure if it is possible.
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stopThe 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??
ugh!
<g>
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.