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.
