Thread: countif depending on three condictions (excel 2000)

1. countif depending on three condictions (excel 2000)

Hi all
How can I count the items depending on the contents in three columns.
just something like this
=countif(a:a,"=apple" and (b:b,">1") and (c:c,this cell is empty))
how can I do this function correctly?
thank you

2. Re: countif depending on three condictions (excel 2000)

COUNTIF only works with a single condition. You will need an array formula to do this. Something like this:

<pre>=SUM((A1:A100="apple")*(B1:B100>1)*(C1:C100=" "))
</pre>

You must hold down the Shift and Ctrl keys when you hit Enter to make that an array formula.

3. Re: countif depending on three condictions (excel 2000)

Hi Legare
that is perfect for my case, thank you, but
1) that does not work with excel 97(I have other older computer)
2) if I change the condition to or (count items meet any one of the condition)
any help will be appreciated.

4. Re: countif depending on three condictions (excel 2000)

The array-formula Legare suggested should work in Excel 97: It needs to be array-entered -- that is, as described to you, you need to hit control+shift+enter at the same time, not just enter.

What follows is an equivalent formula that can be entered normally:

=SUMPRODUCT((A1:A100="apple")*(B1:B100>1)*(LEN(C1: C100)=0))

5. Re: countif depending on three condictions (excel 2000)

I don't know of any reason why that formula would not work on Excel 97. I use to use formula like that in 97 all the time. However, I don't have Excel 97 available so I can't try it.

The formula below should give you the or condition:

<pre>=SUM((((A1:A100="apple")+(B1:B100>1)+(C1:C100 =""))>0)*1)
</pre>

6. Re: countif depending on three condictions (excel 2000)

Those formula's also work in XL5/95.

7. Re: countif depending on three condictions (excel 2000)

Are you sure that you entered it as an array formula: Enter<pre>=SUM((A1:A100="apple")*(B1:B100>1)*(C1:C 100=""))</pre>

in the formula bar. Then press the <Ctrl> <Shift> and <Enter> keys at the same time. If you do this, Excel adds braces around the formula and you should get the correct results. --Sam

8. Re: countif depending on three condictions (excel 2000)

I found out why that is not working in excel 97 because my data has head row(a1is item, b1 is qty, c1is delivery date), and I need to change the range from a1:a100 to a2:a100, b1:b100 to b2:b100, c1:c100 to c2:c100. still do not understand why I need to do so.
thank you all

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•