Count(If(... formula not working properly

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
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
 
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
 
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
your result will be 1
Click to expand...
 
Thanks Max. Works great!

Max said:
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
Click to expand...
 
Back
Top