Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E2 1="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.

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

Dave
--
Brevity is the soul of wit.


" wrote:

Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E2 1="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.

On 1 feb, 14:33, Dave F wrote:
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.

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

--
*****
birds of the same feather flock together..



" wrote:

Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E2 1="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.

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

--
*****
birds of the same feather flock together..



" wrote:

Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E2 1="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.

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

--
*****
birds of the same feather flock together..



" wrote:

Dutch:
=SOMPRODUCT(ALS(E3:E21="Mark";B3:B21;"");ALS(E3:E2 1="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.