Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countifs with multiple dates
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
|
|||
|
|||
countifs with multiple dates
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countifs with multiple dates
Hi,
In 2003: =SUMPRODUCT(--(Presentations=--"3/1/08"),--(Presentations<=--"3/31/08")) or =COUNTIF(Presentations,"=3/1/08")-COUNTIF(Presentations,"3/31/08") In 2007 (the above or): =COUNTIFS(Presentations,"=3/1/08",Presentations,"<=3/31/08") -- If this helps, please click the Yes button Cheers, Shane Devenshire "majestyk" wrote: 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 | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
countifs Text and Dates | Excel Worksheet Functions | |||
Countifs and multiple columns... | Excel Worksheet Functions | |||
Multiple countifs | Excel Worksheet Functions | |||
COUNTIFs with multiple criteria | Excel Discussion (Misc queries) |