# Count cells based on date range in another column

TFTAJLLYMXZP@spammotel.com
Guest
Posts: n/a

 5th May 2005
Could someone please suggest how to use a worksheet function to count
the number of non-empty cells in one column when the value in another
(date) column is in a particular date range? The first column happens
to be dates as well, but the formula would ideally work regardless of
the counted column's data type.

e.g.

Given data such as:
Column 1 Column 2
--------- --------
23-Oct-03 2-Jan-04
12-Dec-03
1-Jan-05 14-Feb-05
31-Oct-02 12-Dec-03
1-Jan-05 14-Feb-05
31-Oct-02 12-Dec-03

How do I count the non-empty cells in Column 1 when Column 2 contains a
date in the year 2003?

Thanks,

Terry

=?Utf-8?B?UGVvIFNqb2Jsb20=?=
Guest
Posts: n/a

 5th May 2005
=SUMPRODUCT(--(YEAR(C2:C10)=2003),--(B2:B10<>""))

where C2:C10 is column 2 and B2:B10 column 1

Regards,

Peo Sjoblom

"(E-Mail Removed)" wrote:

> Could someone please suggest how to use a worksheet function to count
> the number of non-empty cells in one column when the value in another
> (date) column is in a particular date range? The first column happens
> to be dates as well, but the formula would ideally work regardless of
> the counted column's data type.
>
> e.g.
>
> Given data such as:
> Column 1 Column 2
> --------- --------
> 23-Oct-03 2-Jan-04
> 12-Dec-03
> 1-Jan-05 14-Feb-05
> 31-Oct-02 12-Dec-03
> 1-Jan-05 14-Feb-05
> 31-Oct-02 12-Dec-03
>
> How do I count the non-empty cells in Column 1 when Column 2 contains a
> date in the year 2003?
>
> Thanks,
>
> Terry
>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post barwald.simon@gmail.com Microsoft Excel Discussion 2 10th Apr 2013 11:56 PM Brian Microsoft Excel Worksheet Functions 28 17th May 2009 02:58 PM Brian Microsoft Excel Worksheet Functions 0 16th May 2009 04:44 PM Pierre Microsoft Excel Worksheet Functions 5 31st Oct 2007 01:28 AM =?Utf-8?B?ZGF2ZSByb3Ro?= Microsoft Excel Worksheet Functions 2 29th Mar 2005 05:33 PM

Features