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

Advertisements

M

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

Guest

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top