Counting multiple columns using dates and text

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Counting multiple columns using dates and text

  1. #1
    New Member
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting multiple columns using dates and text

     
    J Q
    01/10/2001 Participating
    03/20/2001 Declined
    12/23/2003 Pending
    03/04/2004 Participating
    02/06/2004 Participating

    I am trying to attempt to have excel count only the colums that have the "year" 2004 and are participating on my study. I have tried several different formulas but they are not working for me. Also, I wanted to note that I want this information to go on another worksheet located under a different file name. So, basically this worksheet will be updated daily, from information entered from another worksheet.

    Here's one of the formula's I have tried so far ...

    =SUMPRODUCT(('[Database Reports2.xls]BCA Mailing List'!$J$2:$J$557="2004")*('[Database Reports2.xls]BCA Mailing List'!$Q$2:$Q$557="Participating"))

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting multiple columns using dates and text

    Try,

    =SUMPRODUCT(--(YEAR(A1:A4)=2004),--(B1:B4="participating"))

  3. #3
    New Member
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting multiple columns using dates and text

    Thanks Brian that worked, but, my other problems is where do I insert the file name that the other information is coming from.

    For example ...

    I have this report that I want to be automatically updated from my database I enter data on a daily basis, but, both of these work sheets are located on different files. So, in other words I want my file named BCA monthly report to pull data from the file named BCA database.

    So, my question is .. where in this formula do I put the file name BCA database? =SUMPRODUCT(--(YEAR(A1:A4)=2004),--(B1:B4="participating"))

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,487
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting multiple columns using dates and text

    Quote Originally Posted by pebble1969
    Thanks Brian that worked, but, my other problems is where do I insert the file name that the other information is coming from.

    For example ...

    I have this report that I want to be automatically updated from my database I enter data on a daily basis, but, both of these work sheets are located on different files. So, in other words I want my file named BCA monthly report to pull data from the file named BCA database.

    So, my question is .. where in this formula do I put the file name BCA database? =SUMPRODUCT(--(YEAR(A1:A4)=2004),--(B1:B4="participating"))
    =SUMPRODUCT(--(YEAR('[Database Reports2.xls]BCA Mailing List'!$J$2:$J$557)=2004),--('[Database Reports2.xls]BCA Mailing List'!$Q$2:$Q$557="Participating"))

  5. #5
    New Member
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting multiple columns using dates and text

      
    Thank you soooo much! ...

    This worked perfect

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com