Remember Me?

#1
March 31st 06, 07:27 PM posted to microsoft.public.excel.misc
 wurstfreund Posts: n/a
Easy way to count cells within date range

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

#2
March 31st 06, 07:41 PM posted to microsoft.public.excel.misc
 tjtjjtjt Posts: n/a
Easy way to count cells within date range

=SUMPRODUCT(--(G1:G466=K1),--(G1:G466<=K2))

Where the dates are in G1:G466 and the comparison dates are in K1 and K2.

--
tj

"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

#3
March 31st 06, 07:41 PM posted to microsoft.public.excel.misc
 Ken Wright Posts: n/a
Easy way to count cells within date range

With your dates in say A1:G20, and the two date paremeters in say A26 and
A27 (Earliest in A26, latest in A27)

=COUNTIF(A1:G20,""&A25)-COUNTIF(A1:G20,"="&A26)

This will count dates that fall between those two.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"wurstfreund" wrote in message
...
Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

#4
March 31st 06, 07:42 PM posted to microsoft.public.excel.misc
 Peo Sjoblom Posts: n/a
Easy way to count cells within date range

The easiest way is either

=COUNTIF(A2:A100,"="&DATE(2006,1,1))-COUNTIF(A2:A100,""&DATE(2006,3,31))

=SUMPRODUCT(--(A2:A100=DATE(2006,1,31)),--(A2:A100<=DATE(2006,3,31)))

will count dates from Jan 1st 2006 to Mar 31st 2006 in A2:A100

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

"wurstfreund" wrote in message
...
Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

#5
March 31st 06, 07:44 PM posted to microsoft.public.excel.misc
 Ron Coderre Posts: n/a
Easy way to count cells within date range

Here's one way:

With a list of dates in A1:A100

B1: Start date for the date range
B2: End date for the date range

C1: =SUMPRODUCT(--(((A1:A100<=B2)*A1:A100)=B1))
Returns the count of dates in A1:A100 that are within the date range defined
by B1:B2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

#6
March 31st 06, 07:48 PM posted to microsoft.public.excel.misc
 Gary''s Student Posts: n/a
Easy way to count cells within date range

Its hard to beat COUNTIF():

=COUNTIF(A:A,"<upperlimit")-COUNTIF(A:A,"<lowerlimit")
--
Gary's Student

"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post John Excel Discussion (Misc queries) 3 March 20th 06 04:58 PM Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM mrarcade Excel Worksheet Functions 1 August 3rd 05 10:36 AM Naz Excel Discussion (Misc queries) 0 May 29th 05 10:28 PM Brenda Excel Worksheet Functions 1 December 12th 04 10:00 PM

All times are GMT +1. The time now is 11:09 PM.