My function isn't working. The calculation comes out wrong. I'm trying to use the COUNTIF function in Excel 2003. Here is what I'm trying to do. I want to calculate how many occurences there are within two conditions. See below for example: A B

1 Apples 8/22/2008

2 Oranges 9/13/2008

3 Apples 8/13/2008

4 Pears 8/13/2008

5 Apples 9/3/2008I want to find how many occurences there are for apples that don't expire 9/1/2008. The result should be 2. I'm trying to use the following formula:

=COUNTIF(B1:B5, "<9/1/2008")+COUNTIF(A1:A5, "Apples")

It's not working. Any help would greatly be appreciated. Thanks.

Leticia Rabor

I know that COUNTIF doesn't work with multiple conditions - you probably need an array formula. However, before I suggest something, I need an explanation as to how you get "2" as an answer.

Your text says "apples that don't expire 9/1/2008". I see 2 dates that are before 9/1/2008, but I'm not sure how that relates to "apples that don't expire 9/1/2008"

I'm sorry, what I'm trying to determine is to calculate how many occurences there are with apples that are before 9/1/2008 which calculates to the number 2. Leticia Rabor

Try this array formula: =SUM((A1:A5="Apples")*(B1:B5<DATEVALUE("9/1/2008")))

Put this formula in a cell and hit Ctrl-Shift-Enter which will turn it into an array formula and put brackets {} around it. If you need to edit the formula, you'll need to use Ctrl-Shift-Enter again for it to work.

Ask Your Question

Weekly Poll

Do you believe Microsoft when it says it views the "Desktop" as the core of Windows?

Discuss in The Lounge

Poll History