Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
wurstfreund
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
tjtjjtjt
 
Posts: n/a
Default Easy way to count cells within date range

How about:
=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   Report Post  
Posted to microsoft.public.excel.misc
Ken Wright
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default 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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
UDF is updateing cells on another sheet with count from current sheet. John Excel Discussion (Misc queries) 3 March 20th 06 04:58 PM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM
Is there an easy way to count when adjacent cells contain same data? mrarcade Excel Worksheet Functions 1 August 3rd 05 10:36 AM
Character Count Range of Cells Naz Excel Discussion (Misc queries) 0 May 29th 05 10:28 PM
I need to count the occurence of text in a range of cells (which . Brenda Excel Worksheet Functions 1 December 12th 04 10:00 PM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"