Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple dates in a range
I need to count the number of occurences of a range of dates eg from 1st to
31st of a given month OR set a criteria to count the occurences between 2 dates eg 1/3/08 to 7/3/08 inclusive. The named range is set to presentations and i have tried countif/sumif and conditional sum wizard yet to no avail. What I have come close with, but still gives a wrong answer: =COUNTIF(Presentations,AND("="&"01 Mar 08","<="&"31 Mar 08")) any help appreciated (dates are dd/mm/yy in Australia) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple dates in a range
Try this:
=COUNTIF(Presentations,"="&DATE(2008,3,1))-COUNTIF(Presentations,""&DATE(2008,3,31)) Better to use cells to hold the date boundaries: A1 = lower date boundary = 1/3/2008 (d/m/y) B1 = upper date boundary = 31/3/2008 (d/m/y) =COUNTIF(Presentations,"="&A1)-COUNTIF(Presentations,""&B1) If you want to count for a specific month and the dates all fall within the same year, or the year doesn't matter: =SUMPRODUCT(--(MONTH(Presentations)=3)) Counts all dates in the month of March of *any* year. -- Biff Microsoft Excel MVP "majestyk" wrote in message ... I need to count the number of occurences of a range of dates eg from 1st to 31st of a given month OR set a criteria to count the occurences between 2 dates eg 1/3/08 to 7/3/08 inclusive. The named range is set to presentations and i have tried countif/sumif and conditional sum wizard yet to no avail. What I have come close with, but still gives a wrong answer: =COUNTIF(Presentations,AND("="&"01 Mar 08","<="&"31 Mar 08")) any help appreciated (dates are dd/mm/yy in Australia) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Empty Dates within a Range? | Excel Worksheet Functions | |||
Counting dates within a specified range | Excel Discussion (Misc queries) | |||
counting valid dates in a range of cells | Excel Worksheet Functions | |||
Counting dates in a RANGE (yargh!) :) | Excel Worksheet Functions | |||
Counting Dates in a Range | Excel Discussion (Misc queries) |