+ Reply to Thread
Results 1 to 5 of 5

Using count() with embeded logic

  1. #1
    Registered User
    Join Date
    11-19-2006
    Posts
    2

    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.

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

    Following should do the job ... adjust ranges to your needs ...
    Please Login or Register  to view this content.
    HTH
    Carim

  3. #3
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    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

  4. #4
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    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

  5. #5
    Registered User
    Join Date
    11-19-2006
    Posts
    2
    Thanks guys, that worked perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1