cat blue 2005
dog red 2005
cat <empty> 2005
dog yellow 2005
canary <empty> <empty>
dog <empty> 2004
The animal column is range named "animal." The years are dates (2/2/2005) custom formatted to display as "yyyy". That implies they're numeric, and should register to the count() function.
I need a count of the rows where a dog visited in 2005. I have some examples that translate to:
=COUNT(IF((animal="dog")*(visit>=("1/1/2005"))*(visit<("1/1/2006")),visit))
The sumproduct is the ideal formula for what you are wanting to do
=SUMPRODUCT((Animal="dog")*(YEAR(Visit)=2005))
What it does is loop through the range Animal and through the range Visit. It returns 1 if there is a match, and 0 if there is no match. Therefore if either is 0, then it returns 0, and it returns 1 only if they are both true. It then sums up all the 1's giving you 2 (the answer you are looking for).
Doh! pipped at the post by Carim again. You will need to use year(visit)=2005 as you have a date in your cells (ie 2/2/2005) and that will not equal 2005.
Bookmarks