Can someone explain why INDEX is necessary here?

[bird333]

I created this formula where I wanted to know if all the conditions were true or false. The only way I could get this to work was to put the array inside an INDEX function. Some functions will work without INDEX, but ISNUMBER will not. What is INDEX doing that allows this to work? Is there a way I can eliminate the INDEX function? Formula below

=SUMPRODUCT(--AND(INDEX((""=$X$16:$X20)*ISNUMBER($Z$16:$Z20),)))

[Pete_UK]

Try it like this:

=SUMPRODUCT(($X$16:$X20="")*(ISNUMBER($Z$16:$Z20)))

Hope this helps.

Pete

[Glenn Kennedy]

Oooh... it looks REALLY odd that way round!!

=SUMPRODUCT(--AND(INDEX(($X$16:$X20="")*ISNUMBER($Z$16:$Z20),)))

is more conventional. anyway. INDEX is adding the bits in red. Yes, you can take it out, but you may have to enter it as an array formula to get it to work. Thats why it's there. IMHO... leave it there.

[bird333]

I'm not sure why I had the formula arranged in that order. Of course now I can't find the spreadsheet that I used this for. I keep a spreadsheet of formulas I figure out in case I ever have to use them again. That's where this came from.

@Pete_UK I don't think your formula will give me a FALSE if all the operations don't evaluate to TRUE. For example, if after all the calculations I get an array of answers like this {0,1,0,0,1,0}. I want to get a FALSE which is why I'm using the 'AND' function. I could be wrong because like I said I can't find the spreadsheet I was trying to use this on. But what I still don't get is using the same formula (without the INDEX part) works with some functions but with a function like 'ISNUMBER' it doesn't work and I have to put it in.

[Glenn Kennedy]

... because it's an array formula under those crcumstances and needs to be set with CTRL-SHIFT-ENTER and not just ENTER

[bird333]

... because it's an array formula under those crcumstances and needs to be set with CTRL-SHIFT-ENTER and not just ENTER

And so ISNUMBER is one of those functions that fails with arrays?