Question: Using "OR" in SUMPRODUCT Doesn't Work

[Matthew]

I have an equation using SUMPRODUCT on a table, and when I modify it to
include an "OR" function, it no longer works.

My original function:
=SUMPRODUCT(Table1[Column1]*ISBLANK(Table1[Column2]))

Modified Function:
=SUMPRODUCT(Table1[Column1]*OR(ISBLANK(Table1[Column2])))

The modified function returns "0"

Any suggestions on why my modified function doesn't work?

I'm really going to be using the "OR" with the following:
=SUMPRODUCT(Table1[Column1]*OR(ISBLANK(Table1[Column2]),Table1[Column2]<Date1))

Thanks,

 

[Pete_UK]

With SUMPRODUCT the use of * to join two conditions together is
equivalent to AND, and you use + to set up the OR condition. This is
because the criteria evaluate to TRUEs and FALSEs.

Take a look at Bob Phillips' site for more information:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete

 

[T. Valko]

I'm really going to be using the "OR" with the following:

=SUMPRODUCT(Table1[Column1]*OR(ISBLANK(Table1[Column2]),Table1[Column2]<Date1))

Structured references = Yuk!

I assume Table1[Column1] are numbers.

Try this:

=SUMPRODUCT(Table1[Column1],SIGN(ISBLANK(Table1[Column2])+(Table1[Column2]<Date1)))

--
Biff
Microsoft Excel MVP

I have an equation using SUMPRODUCT on a table, and when I modify it to
include an "OR" function, it no longer works.

My original function:
=SUMPRODUCT(Table1[Column1]*ISBLANK(Table1[Column2]))

Modified Function:
=SUMPRODUCT(Table1[Column1]*OR(ISBLANK(Table1[Column2])))

The modified function returns "0"

Any suggestions on why my modified function doesn't work?

I'm really going to be using the "OR" with the following:
=SUMPRODUCT(Table1[Column1]*OR(ISBLANK(Table1[Column2]),Table1[Column2]<Date1))

Thanks,