Using count() with embeded logic

This is my data:

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))

and:
=COUNT(IF(AND(animal="dog",visit>=("1/1/2005"),visit<("1/1/2006")),1))

These return 0; some variations return 1. Obviously there were 2 dog visits in 2005, but I'm having problems figuring out the syntax. Can anyone help?

Thanks in advance. Apologies for duplicate posts, my previous thread #1722448 seems to have vanished into thin air.

Hi,

Following should do the job ... adjust ranges to your needs ...

Please Login or Register  to view this content.

HTH
Carim

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).

Matt

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.

Matt

Thanks guys, that worked perfectly.