If statement for array columns

Question

Hi all

I need a formula that returns me a true or false statement for an array column.

The statement is, if the product you select is water and the columns S and T contain numbers, then you need to remove the numbers, if you select water and columns T and S are empty then you are ok.

I used this formula

=IF(SUMPRODUCT(--('Fountain template carga'!N:N="Water"),--('Fountain template carga'!S3:T900>0))=0,"Ok","Remove values")

I keep getting a  #Value!

This is the excel reference, sheet name foutain template carga:

How can i make it work?

Thanks

Answer 1

Try this one:

=IF(SUMPRODUCT(('Fountain template carga'!N3:N900="Water")*('Fountain template carga'!S3:S900+'Fountain template carga'!T3:T900))=0,"Ok","Remove values")

Andreas.

Answer 2

Seem to work, so basically I have to use the + sign per column? There is no way to drag the whole matrix from S to V correct?

When you use multiple columns means 1*0*0*1*0 = 0, but 1+0+0+1+0 = 2 that's the math behind it.

Andreas.

Answer 3

Hi Andreas

Seem to work, so basically I have to use the + sign per column? There is no way to drag the whole matrix from S to V correct?

Thanks!