Counting multiple columns using dates and text

[pebble1969]

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

 

[Brian from Maui]

Try,

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

 

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

 

[Aladin Akyurek]

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

 

[pebble1969]

Thank you soooo much! ...

This worked perfect