sumproduct problem

[Guest]

hello,

i've been reading up on all the sumproduct related posts, but can't figure
out what's wrong with mine...

this is my formula:
=SUMPRODUCT(Détail!B2:B151=1;Détail!AB2:AB151=1;Détail!T2:T151)

when i look at the insert function arguments box to help define the arrays,
excel returns the right values, i.e.:
for array 1 : false, true, false, false,.. (this is the first condition)
for array 2 : true, true, true, true,... (this is the second condition)
for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up)

so i thought excel would return '2', i.e. for the second record in each array:
true * true * 2 = 2
however, the value returned = 0

i also tried using the following
=SUMPRODUCT(Détail!B2:B151=1*Détail!AB2:AB151=1;Détail!T2:T151)
but the result is the same.

thanks for your help.
andy

 

[JE McGimpsey]

SUMPRODUCT requires numeric arguments, while your first two are boolean
(TRUE/FALSE). Try:

=SUMPRODUCT(--(Détail!B2:B151=1);--(Détail!AB2:AB151=1);Détail!T2:T151)

See

http://www.mcgimpsey.com/excel/doubleneg.html

for a more extensive explanation.

 

[Guest]

many thanks!
problem solved...

SUMPRODUCT requires numeric arguments, while your first two are boolean
(TRUE/FALSE). Try:

=SUMPRODUCT(--(Détail!B2:B151=1);--(Détail!AB2:AB151=1);Détail!T2:T151)

See

http://www.mcgimpsey.com/excel/doubleneg.html

for a more extensive explanation.