Excel 2003 COUNTIF

Intel
October 6, 2008 at 10:23:31
Specs: Win XP, Core Duo 2Gb
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/2008

I 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


See More: Excel 2003 COUNTIF

Report •


#1
October 6, 2008 at 12:18:00
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"


Report •

#2
October 6, 2008 at 12:37:40
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


Report •

#3
October 6, 2008 at 13:40:02
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.


Report •

Related Solutions


Ask Question