problem using array formula in excel

[diana]

I have been leaning to use array formulas. As an example, i have a simple
2-column situation with data in cells a1,2,a3,a4,b1,b2,b3,b4 as follows:
a1=apple, a2=apple, a3=orange,a4=orange,b1=red,b2=green,b3=red,b4=green.
(So there are 2 apples and 2 oranges of which one of each is red, and the
other green)

When i enter this =SUM(IF(A1:A4="apple",1,0)) array formula in a5 it returns
2 which is correct. However when i enter this
=SUM(IF((A1:A4="apple")+(B1:B4="red"),1,0)) array formula in b5 it also
returns 2 which is incorrect as there is only 1 red apple.
It seems to count values in a single column ok but when trying to combine
info in two columns it disregards the second.

What am i doing wrong?

 

[macropod]

Hi Diana,

The formula:
=SUM(IF((A1:A4="apple")+(B1:B4="red"),1,0))
returns the count of items that are apples or red, which should equal 3.

To get the count of red apples you'd use:
=SUM(IF((A1:A4="apple")*(B1:B4="red"),1,0))

Cheers

 

[little_creature]

Hi Diana,
As macropod corrected your formula, I just wanted to add that this is
statistics and probability math issue. Generaly if you are looking for
intersection of 2 or more actions:

IF you want to count when one or the other case come into existence then use
+
(> count of items that are apples or red, which should equal 3)


IF you want to count when both cases come into existence simultaneously then
use *
(count of apples that are red)

Hi Diana,

The formula:
=SUM(IF((A1:A4="apple")+(B1:B4="red"),1,0))
returns the count of items that are apples or red, which should equal 3.

To get the count of red apples you'd use:
=SUM(IF((A1:A4="apple")*(B1:B4="red"),1,0))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I have been leaning to use array formulas. As an example, i have a simple
2-column situation with data in cells a1,2,a3,a4,b1,b2,b3,b4 as follows:
a1=apple, a2=apple, a3=orange,a4=orange,b1=red,b2=green,b3=red,b4=green.
(So there are 2 apples and 2 oranges of which one of each is red, and the
other green)

When i enter this =SUM(IF(A1:A4="apple",1,0)) array formula in a5 it returns
2 which is correct. However when i enter this
=SUM(IF((A1:A4="apple")+(B1:B4="red"),1,0)) array formula in b5 it also
returns 2 which is incorrect as there is only 1 red apple.
It seems to count values in a single column ok but when trying to combine
info in two columns it disregards the second.

What am i doing wrong?