Sumproduct query

[Jack Schitt]

I have named ranges as follows:
Area refers to $C$11:$C$22
Option refers to $D$11:$D$22
Pessimistic refers to $E$11:$E$22

Formula
=SUMPRODUCT(Pessimistic*(Area=$C4)*(Option=$D4))
Returns the correct result, being the same as
=SUBTOTAL(9,Pessimistic)
after autofiltering database $C$11:$E$22 on Area set to value in C4 and
Option set to value in D4.

However, formula
=SUMPRODUCT(Pessimistic,Area=$C4,Option=$D4)
Returns value zero (not desired).

Could someone please tell me what is different about the logical process in
evaluating SUMPRODUCT function in each of the above cases? I understand the
process in the first case (multiplication separator) but clearly not the
second (comma separator).

Thanks

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(Pessimistic,--(Area=$C4),--(Option=$D4))

Rem: -- forces logical expressions (TRUE/FALSE) to their numeric equivalents
(1 or 0).
Maybe you can use also
=SUMPRODUCT(Pessimistic,(Area=$C4)*1,(Option=$D4)*1)
, but I'm not entirely sure about it - havn't tested it in such setup.

 

[Frank Kabel]

Hi Arvi
the formula
=SUMPRODUCT(Pessimistic,(Area=$C4)*1,(Option=$D4)*1)

will work as will:
=SUMPRODUCT(Pessimistic,(Area=$C4)+0,(Option=$D4)+0)
or
=SUMPRODUCT(Pessimistic,(Area=$C4)^1,(Option=$D4)^1)
or even
=SUMPRODUCT(Pessimistic,N(Area=$C4),N(Option=$D4))

But I personally prefer the 'double minus'

 

[Arvi Laanemets]

Hi Frank

Hi Arvi
the formula
=SUMPRODUCT(Pessimistic,(Area=$C4)*1,(Option=$D4)*1)

will work as will:

I was almost sure about it.


Arvi Laanemets

 

[Jack Schitt]

Thanks Frank and Arvi

Hi Frank





I was almost sure about it.


Arvi Laanemets