Single-cell array calculation bug in Excel?

[Jeff]

This is driving me nuts - I hope someone can help.

Here is a simplification of the problem:

A B C D E
1 Item Amount Item Type
2 Banana 11 Apple Fruit
3 Apple 22 Banana Fruit
4 Brocolli 33 Brocolli Vegetable
5 Apple 44
6 Brocolli 55
7 Total 165
8 Total Fruit 165

I want the total of only Fruit in a single cell. If I array-enter
the following formula in a single cell (B8):

=SUM(IF(VLOOKUP(A2:A6,D2:E4,2)="Fruit",1,0)*(B2:B6))

it produces an incorrect answer of 165 (i.e. it counts all elements
of the array). However, if I array-enter the same exact formula in
two or more cells (say, B8:B9), it produces the correct answer of 77 in all cells.

This is strange. My guess is that as a single cell formula, it is
evaluating the VLOOKUP comparison for only the first element of the
array, while the multi-cell version is actually doing the comparison
for each element.

The question, of course, is how can I get this to work in a single
cell, or does anyone have any alternate suggestions to accomplish
this?

Thanks in advance.
Jeff

 

[Alan]

This is driving me nuts - I hope someone can help.

Here is a simplification of the problem:

A B C D E
1 Item Amount Item Type
2 Banana 11 Apple Fruit
3 Apple 22 Banana Fruit
4 Brocolli 33 Brocolli Vegetable
5 Apple 44
6 Brocolli 55
7 Total 165
8 Total Fruit 165

I want the total of only Fruit in a single cell. If I array-enter
the following formula in a single cell (B8):

=SUM(IF(VLOOKUP(A2:A6,D2:E4,2)="Fruit",1,0)*(B2:B6))

it produces an incorrect answer of 165 (i.e. it counts all elements
of the array). However, if I array-enter the same exact formula in
two or more cells (say, B8:B9), it produces the correct answer of 77
in all cells.

This is strange. My guess is that as a single cell formula, it is
evaluating the VLOOKUP comparison for only the first element of the
array, while the multi-cell version is actually doing the comparison
for each element.

The question, of course, is how can I get this to work in a single
cell, or does anyone have any alternate suggestions to accomplish
this?

Thanks in advance.
Jeff

Hi Jeff,

There are probably lots of ways of doing this, and some more elegant
than mine, but nevertheless, I think this does what you want:

{=SUM((A2:A6=TRANSPOSE(IF(D24="Fruit",C2:C4,"XXX")))*(B2:B6))}

Array-entered without the braces of course.

"XXX" could be anything that is not going to be in column D, but I
personally tend to avoid an empty string "" just in case it causes
problems - that is a personal choice though.

HTH,

Alan.

 

[Domenic]

=SUM(IF(LOOKUP(A2:A6,C24)="Fruit",B2:B6))

...entered using CONTROL+SHIFT+ENTER

OR

=SUMPRODUCT(--(LOOKUP(A2:A6,C24)="Fruit"),B2:B6)

Hope this helps!

 

[Frank Kabel]

Hi
just as explanantion why LOOKUP works and VLOOKUP does not: Problem is
that VLOOKUP using an array as first entry DOES NOT return an array as
result (but something different). You could test this if you use F9 in
the formula bar.