Count(If(... formula not working properly

[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?

 

[Max]

Your formula: =COUNT(IF(a1:a3="md",b1:b3))
is an array formula.

You need to array-enter the formula by
pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

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

 

[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
your result will be 1

 

[Guest]

Works just as you describe. Thanks!!

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

 

[Guest]

Thanks Max. Works great!

Your formula: =COUNT(IF(a1:a3="md",b1:b3))
is an array formula.

You need to array-enter the formula by
pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

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

 

[Max]

Great to hear that !
Thanks for the feedback ..