# Count(If(... formula not working properly

G

#### Guest

FORMULA NOT DISPLAYING CORRECT RESULT

The following is much simplified over what I actually need to do, but
illustrates the inconsistent behavior of the formula.

SETUP
â€¢ In cells a1:a3 enter md,fl,tx.
â€¢ In cells b1:b3 enter 10,20,30.
â€¢ In another cell enter the formula =COUNT(IF(a1:a3="md",b1:b3))

My Excel 2002 displays a value result of 0. However, if you click on the
formula cell and then select Insert|Function, Excel breaks the formula down
like this:

{10;FALSE, FALSE}
Formula result = 1

The formula decomposition is what I would expect (and what I want), a one â€“
yet the actual display is a zero. How do I get the display to work properly?

M

#### Max

is an array formula.

You need to array-enter the formula by
pressing CTRL+SHIFT+ENTER

But think we could also just use,
as an alternative here:
=SUMPRODUCT(--(A1:A3="md"))
which doesn't need to be array-entered

G

#### Guest

Click in your formaula bar, as if to edit your formula. Now press
<Ctrl><Shift><Enter> Excel will insert {} brackets around your argument, and

G

#### Guest

Works just as you describe. Thanks!!

kassie said:
Click in your formaula bar, as if to edit your formula. Now press
<Ctrl><Shift><Enter> Excel will insert {} brackets around your argument, and

G

#### Guest

Thanks Max. Works great!

Max said:
is an array formula.

You need to array-enter the formula by
pressing CTRL+SHIFT+ENTER

But think we could also just use,
as an alternative here:
=SUMPRODUCT(--(A1:A3="md"))
which doesn't need to be array-entered

M

#### Max

Great to hear that !
Thanks for the feedback ..