A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Easy way to count cells within date range



 
 
Thread Tools Display Modes
  #1  
Old March 31st 06, 07:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
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?
Ads
  #2  
Old March 31st 06, 07:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old March 31st 06, 07:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old March 31st 06, 07:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old March 31st 06, 07:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old March 31st 06, 07:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03: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 09:00 PM


All times are GMT +1. The time now is 06:25 PM.


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