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 a speed of 25Mbps or higher is necessary for a connection to be considered broadband?

Discuss in The Lounge

Poll History