sumproduct + index

[bluepenink]

hello

having some trouble with this formula...not sure why the error is appearing.

$ of all invoices:

=SUMPRODUCT(--(INDEX(_Data,0,10)>=$D$10),--(INDEX(_Data,0,10)<=$D$10+6),--(INDEX(_Data,0,12)))

 

[Jonmo1]

Not sure if it's the problem, but the sumrange doesn't have to be in the --(...) syntax..

Should be just
=SUMPRODUCT(--(INDEX(_Data,0,10)>=$D$10),--(INDEX(_Data,0,10)<=$D$10+6),INDEX(_Data,0,12))

Also, make sure the named range _Data is NOT referring to ENTIRE columns..


If this doesn't help,
What is the error?
Are there any errors within the referenced ranges? Those will propegate into the result of the formula.

 

[bluepenink]

hello thx u so much...that did the trick *duh*, thx u so much!

also, this formula below, im not sure why its giving me #value error. can u pls advise

# of invoices per store, based on date in D10 (d10 = start of week i.e. monday only may-30, june 6 etc)

=AVERAGE(IF(FREQUENCY(IF(INDEX(_Data,0,10)>=$D$10,IF(INDEX(_Data,0,10)<=$D$10+6,IF(INDEX(_Data,0,11),MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0)))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,1))

confirmed by crt+shift+enter

thx u so much

 

[bluepenink]

hello guys any suggestions? thxs

 

[Aladin Akyurek]

hello guys any suggestions? thxs

1. What is the definition of _Data?

2. Is there any formula in the area _Data covers?

3. What is in D10?

4. What gives =D10+0?

 

[bluepenink]

Hello Aladin,

a)
_Data is where my data is contained
='G.S - Report'!$C$8:INDEX('G.S - Report'!$W:$W,MATCH(REPT("Z",255),'G.S - Report'!$C:$C))

b) yes there are formulas in some ranges

c) D10 contains the the date for the first monday of every week i.e. 30-may-2011, 6-june-2011 etc, from a drop down list)

d) not sure about the D10+0

so basically,

# of invoices per store, based on date in D10 (d10 = start of week i.e. monday only may-30, june 6 etc)

=AVERAGE(IF(FREQUENCY(IF(INDEX(_Data,0,10)>=$D$10,IF(INDEX(_Data,0,10)<=$D$10+6,IF(INDEX(_Data,0,11),MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0)))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,1))

confirmed by crt+shift+enter

*i wish i could upload a file, w.o my server blocking sites ugg*

 

[Aladin Akyurek]

Hello Aladin,

a)
_Data is where my data is contained
='G.S - Report'!$C$8:INDEX('G.S - Report'!$W:$W,MATCH(REPT("Z",255),'G.S - Report'!$C:$C))

OK.

b) yes there are formulas in some ranges

Would you post some of them here?

c) D10 contains the the date for the first monday of every week i.e. 30-may-2011, 6-june-2011 etc, from a drop down list)

OK.

d) not sure about the D10+0
...

Enter in some cell:

=D10+0

and report back the result.

 

[bluepenink]

OK.



Would you post some of them here?



OK.



Enter in some cell:

=D10+0

and report back the result.

D10 + 0 = value in D10

i.e. D10 = 6-June-2011
D10+0 = 6-June-2011

thx u sir!

 

[Aladin Akyurek]

D10 + 0 = value in D10

i.e. D10 = 6-June-2011
D10+0 = 6-June-2011

thx u sir!

And the question/request you skipped...

 

[bluepenink]

And the question/request you skipped...

hello aladin

im not quite, sure D10+0 gives me the same answers as the value in D10, which is 6-june-2011 or whatever the selected date is....am i missing something, sorry?