Array formula

[beamon]

Hi,

Look at this little table:

a 1
z 2
a 3
r 4

I want to count the number of occurences of "a". I know there is a easier way to do that using COUNTIF, but i'm experimenting with array formula and i did it in two ways:

COUNT((L7:L10="a")*(M7:M10)) -> this gives 4 !?!
COUNT(IF(L7:L10="a";M7:M10)) -> this gives 2

I thought that both formulas were the same (i did the same with SUM and both formulas produce 4). So why does the first formula produce a wrong result?

Thanks
beamon

[zbor]

Try this:

=COUNT(IF(L7:L10="a";ROW(L7:L10)))

Thid doesn't depend of second column. It just calculate number of a in first column.

Your approach might give you different results depending of data in M column

Problem with first formula is because it counts zero's in product as numbers.

Evaluate formula:

=COUNT((L7:L10="a")*(M7:M10))
=COUNT(({TRUE;FALSE;TRUE;FALSE}*(M7:M10))
=COUNT(({TRUE;FALSE;TRUE;FALSE}*{1;2;3;4})
=COUNT({1;0;3;0})
=4

while second formula give this:

=COUNT(IF(L7:L10="a";M7:M10))
=COUNT(IF({TRUE;FALSE;TRUE;FALSE};M7:M10))
=COUNT({1;FALSE;3;FALSE})
=2

[zbor]

Or this, non-array:

=SUMPRODUCT(--($L$7:$L$10="a"))

[beamon]

Thanks for replying