Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF is updateing cells on another sheet with count from current sheet. | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Is there an easy way to count when adjacent cells contain same data? | Excel Worksheet Functions | |||
Character Count Range of Cells | Excel Discussion (Misc queries) | |||
I need to count the occurence of text in a range of cells (which . | Excel Worksheet Functions |