SOMPRODUCT

[Smits]

Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E21="Mark";C3:C21;""))

English (?):
=SUMPRODUCT(IF(E3:E21="Mark";B3:B21;"");IF(E3:E21="Mark";C3:C21;""))

When I examine this function in the "function wizard" (pressing the fx
button), the wizard shows the correct value. When executing the
function in the sheet itself it shows #VALUE! (or whatever it is in
English) as not being able to calculate the formula. It shows me that
evaluating E3:E21 leads to an error.

What is wrong here ? How can it show the correct result in the wizard
but not in the sheet ?

Thanks for any help.

 

[Dave F]

Are you entering this as an array formula (hitting CTRL + SHIFT + ENTER at
the same time?

Dave

 

[Smits]

Are you entering this as an array formula (hitting CTRL + SHIFT + ENTER at
the same time?

Arrgggghhhh. I pressed Enter first and then tried to convert it with
ctrl+shift+enter. It has been to long using this kind of stuff...

Thanks forthe pointer.

 

[driller]

Hello Smits,

have u tried without an IF - will it be okey with this
=SUMPRODUCT(--(E3:E21="Mark"),(B3:B21),(C3:C21))
no need ctrl-shift-enter

 

[driller]

Hello Smits,

have u tried without an IF - will it be okey with this
=SUMPRODUCT(--(E3:E21="Mark"),(B3:B21),(C3:C21))
no need ctrl-shift-enter

 

[driller]

Hello Smits,

have u tried without an IF - will it be okey with this
=SUMPRODUCT(--(E3:E21="Mark"),(B3:B21),(C3:C21))
no need ctrl-shift-enter