SOMPRODUCT
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. |
SOMPRODUCT
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. |
SOMPRODUCT
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. |
SOMPRODUCT
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. |
SOMPRODUCT
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. |
SOMPRODUCT
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. |
All times are GMT +1. The time now is 06:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com