misbehaving array

[methody]

"Hello
In column B under a heading 'letters' I have the numbers 1 2 3 1 in separate cell. In column C under a heading 'cost' I have the numbers 4 5 5 7 in sepate cells. I have named the ranges in columns B and C 'let' and 'cost'. I want a formula for the sum of all those cells in column C which have a '1' in column B. The answer should be 11. When I use an array formula with the curly brackets =SUM(IF(let="1",1,0)*cost) it works. If I put the numbber '1' into a separate cell, say E6 and replace "1" in the formula with E6 it doesn't work. Do you know why as this is what I need to do.
I'd appreciate any thoughts.
methody

 

[jfuoti]

The reason is because the array formula needs to have the same # of components in each list. In effect, with an array formula you are looking at ranges of data, when the ranges are different sizes it won't work because you are asking to look at a range >1 cell and a single cell. You would also get an error if let and cost were different amounts of data. To correct this you would have to have the constant "1" be in a similar sized array. Hope this helps.

 

[methody]

thank you

 

[Aladin Akyurek]

What is the range that let refers to?

 

[Andrew Poulsom]

It seems that the entries in let are text, because you have surrounded the 1 with quotes. If the entry of 1 in E6 is also text this array formula will work:

=SUM(IF(let=E6,1,0)*cost)

So format cell E6 as Text before entering a 1.

Contrary to what jfuoti said, Excel will successfully compare an array with a single cell, just as it does with a single number or string.

 

[methody]

Thany you