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)

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)

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)