countif depending on three condictions (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

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

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

Reply | Quote

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.

Reply | Quote

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))

Reply | Quote

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:

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

Reply | Quote

Those formula’s also work in XL5/95.

Reply | Quote

Are you sure that you entered it as an array formula: Enter

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

in the formula bar. Then press the and keys at the same time. If you do this, Excel adds braces around the formula and you should get the correct results. –Sam

Reply | Quote

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

Reply | Quote