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:
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.
Following should do the job ... adjust ranges to your needs ...
The sumproduct is the ideal formula for what you are wanting to do
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.
Thanks guys, that worked perfectly.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1