Countif using dates

Sounds like the dates are not real dates

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> Bob - spot on, I'm getting a '0' which I can't see the reason for!
>
> "Bob Phillips" wrote:
>
> > If they are dates, the format should not matter at all.
> >
> > What problems are you experiencing? Do you get 0 or an answer which you
> > can't see the reason for?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob - thanks for you prompt response - I'm still having problems, do
you
> > > think it's how I've formatted the cells with the dates in? I've got
them
> > so
> > > that however the user inputs the date it comes up "01-Apr-05" format?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> > > >
> > > > If you are just looking for a month, you can use
> > > >
> > > > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> > > >
> > > > or if there can be multiple yers, then
> > > >
> > > > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> > > >
> > > > or
> > > >
> > > > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Bugaglugs" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > In column A I have dates in the following format;
> > > > > 01-Apr-05 right through until the end of March 2006
> > > > > On a different sheet in my workbook I want to summarise the
> > information
> > > > by
> > > > > counting the number of appointments between certain dates to get
> > monthly
> > > > > totals. I just don't know how to say that I want all dates
between
> > > > 01-Apr-05
> > > > > until say 30-Apr-05p and so on. Is it possible to do this - the
> > formula I
> > > > > was trying with was;
> > > > > =COUNT(IF('Master
> > > > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > > > But this isn't working!
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Make sure that the sheet name referenced in the formula matches exactly
the sheet name in your file.

In article <[email protected]>,
Bugaglugs <[email protected]> wrote:

> Domenic
> Thanks - still puzzling over this as I copied and pasted the formula and am
> now getting #ref
>
> I'm sure this is me rather than you but I just can't understand it!
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> > Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
> >
> > or
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> > Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Bugaglugs <[email protected]> wrote:
> >
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the information
> > > by
> > > counting the number of appointments between certain dates to get monthly
> > > totals. I just don't know how to say that I want all dates between
> > > 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the formula
> > > I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> >

Thank you for all your help - have now found out the reason for my problems
when using all the suggested formulas....am VERY sorry to admit that I'd put
in the wrong date when trying it out so it was a simple case of user error on
my part. Thanks once again for all time taken from everyone!

"Bugaglugs" wrote:

> Domenic
> Thanks - still puzzling over this as I copied and pasted the formula and am
> now getting #ref
>
> I'm sure this is me rather than you but I just can't understand it!
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> > Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
> >
> > or
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> > Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Bugaglugs <[email protected]> wrote:
> >
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the information by
> > > counting the number of appointments between certain dates to get monthly
> > > totals. I just don't know how to say that I want all dates between 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> >

Domenic
Thanks - still puzzling over this as I copied and pasted the formula and am
now getting #ref

I'm sure this is me rather than you but I just can't understand it!

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
>
> or
>
> =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
>
> Hope this helps!
>
> In article <[email protected]>,
> Bugaglugs <[email protected]> wrote:
>
> > In column A I have dates in the following format;
> > 01-Apr-05 right through until the end of March 2006
> > On a different sheet in my workbook I want to summarise the information by
> > counting the number of appointments between certain dates to get monthly
> > totals. I just don't know how to say that I want all dates between 01-Apr-05
> > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > was trying with was;
> > =COUNT(IF('Master
> > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > But this isn't working!
>

Bob - spot on, I'm getting a '0' which I can't see the reason for!

"Bob Phillips" wrote:

> If they are dates, the format should not matter at all.
>
> What problems are you experiencing? Do you get 0 or an answer which you
> can't see the reason for?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
> > Bob - thanks for you prompt response - I'm still having problems, do you
> > think it's how I've formatted the cells with the dates in? I've got them
> so
> > that however the user inputs the date it comes up "01-Apr-05" format?
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> > >
> > > If you are just looking for a month, you can use
> > >
> > > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> > >
> > > or if there can be multiple yers, then
> > >
> > > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> > >
> > > or
> > >
> > > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Bugaglugs" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > In column A I have dates in the following format;
> > > > 01-Apr-05 right through until the end of March 2006
> > > > On a different sheet in my workbook I want to summarise the
> information
> > > by
> > > > counting the number of appointments between certain dates to get
> monthly
> > > > totals. I just don't know how to say that I want all dates between
> > > 01-Apr-05
> > > > until say 30-Apr-05p and so on. Is it possible to do this - the
> formula I
> > > > was trying with was;
> > > > =COUNT(IF('Master
> > > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > > But this isn't working!
> > > >
> > > >
> > >
> > >
> > >
>
>
>

If they are dates, the format should not matter at all.

What problems are you experiencing? Do you get 0 or an answer which you
can't see the reason for?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> Bob - thanks for you prompt response - I'm still having problems, do you
> think it's how I've formatted the cells with the dates in? I've got them
so
> that however the user inputs the date it comes up "01-Apr-05" format?
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> >
> > If you are just looking for a month, you can use
> >
> > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> >
> > or if there can be multiple yers, then
> >
> > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> >
> > or
> >
> > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the
information
> > by
> > > counting the number of appointments between certain dates to get
monthly
> > > totals. I just don't know how to say that I want all dates between
> > 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the
formula I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> > >
> > >
> >
> >
> >

Maybe, but we don't format dates like that in the UK, far better to use an
unambiguous way IMO, such as I showed or even

=SUMPRODUCT(--(A2:A1000>=--"2005-04-01")),--(A2:A1000<=--"2005-04-30"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Umlas" <[email protected]> wrote in message
news:%237RACb%[email protected]...
> This also works:
> =SUMPRODUCT(--(A2:A1000>="4/1/05"),--(A2:A1000<="4/20/05"))
>
> "Bob Phillips" <[email protected]> wrote in message
> news:eUW$3Y%[email protected]...
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> >
> > If you are just looking for a month, you can use
> >
> > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> >
> > or if there can be multiple yers, then
> >
> > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> >
> > or
> >
> > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> >> In column A I have dates in the following format;
> >> 01-Apr-05 right through until the end of March 2006
> >> On a different sheet in my workbook I want to summarise the information
> > by
> >> counting the number of appointments between certain dates to get
monthly
> >> totals. I just don't know how to say that I want all dates between
> > 01-Apr-05
> >> until say 30-Apr-05p and so on. Is it possible to do this - the
formula
> >> I
> >> was trying with was;
> >> =COUNT(IF('Master
> >> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> >> But this isn't working!
> >>
> >>
> >
> >
>
>

Bob - thanks for you prompt response - I'm still having problems, do you
think it's how I've formatted the cells with the dates in? I've got them so
that however the user inputs the date it comes up "01-Apr-05" format?

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
>
> If you are just looking for a month, you can use
>
> =SUMPRODUCT(--(MONTH(A2:A1000)=4))
>
> or if there can be multiple yers, then
>
> =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
>
> or
>
> =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
> > In column A I have dates in the following format;
> > 01-Apr-05 right through until the end of March 2006
> > On a different sheet in my workbook I want to summarise the information
> by
> > counting the number of appointments between certain dates to get monthly
> > totals. I just don't know how to say that I want all dates between
> 01-Apr-05
> > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > was trying with was;
> > =COUNT(IF('Master
> > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > But this isn't working!
> >
> >
>
>
>

This also works:
=SUMPRODUCT(--(A2:A1000>="4/1/05"),--(A2:A1000<="4/20/05"))

"Bob Phillips" <[email protected]> wrote in message
news:eUW$3Y%[email protected]...
> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
>
> If you are just looking for a month, you can use
>
> =SUMPRODUCT(--(MONTH(A2:A1000)=4))
>
> or if there can be multiple yers, then
>
> =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
>
> or
>
> =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
>> In column A I have dates in the following format;
>> 01-Apr-05 right through until the end of March 2006
>> On a different sheet in my workbook I want to summarise the information
> by
>> counting the number of appointments between certain dates to get monthly
>> totals. I just don't know how to say that I want all dates between
> 01-Apr-05
>> until say 30-Apr-05p and so on. Is it possible to do this - the formula
>> I
>> was trying with was;
>> =COUNT(IF('Master
>> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
>> But this isn't working!
>>
>>
>
>

Try...

=SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))

or

=SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))

Hope this helps!

In article <[email protected]>,
Bugaglugs <[email protected]> wrote:

> In column A I have dates in the following format;
> 01-Apr-05 right through until the end of March 2006
> On a different sheet in my workbook I want to summarise the information by
> counting the number of appointments between certain dates to get monthly
> totals. I just don't know how to say that I want all dates between 01-Apr-05
> until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> was trying with was;
> =COUNT(IF('Master
> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> But this isn't working!

=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))

If you are just looking for a month, you can use

=SUMPRODUCT(--(MONTH(A2:A1000)=4))

or if there can be multiple yers, then

=SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))

or

=SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> In column A I have dates in the following format;
> 01-Apr-05 right through until the end of March 2006
> On a different sheet in my workbook I want to summarise the information
by
> counting the number of appointments between certain dates to get monthly
> totals. I just don't know how to say that I want all dates between
01-Apr-05
> until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> was trying with was;
> =COUNT(IF('Master
> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> But this isn't working!
>
>

Sounds like the dates are not real dates

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> Bob - spot on, I'm getting a '0' which I can't see the reason for!
>
> "Bob Phillips" wrote:
>
> > If they are dates, the format should not matter at all.
> >
> > What problems are you experiencing? Do you get 0 or an answer which you
> > can't see the reason for?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob - thanks for you prompt response - I'm still having problems, do
you
> > > think it's how I've formatted the cells with the dates in? I've got
them
> > so
> > > that however the user inputs the date it comes up "01-Apr-05" format?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> > > >
> > > > If you are just looking for a month, you can use
> > > >
> > > > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> > > >
> > > > or if there can be multiple yers, then
> > > >
> > > > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> > > >
> > > > or
> > > >
> > > > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Bugaglugs" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > In column A I have dates in the following format;
> > > > > 01-Apr-05 right through until the end of March 2006
> > > > > On a different sheet in my workbook I want to summarise the
> > information
> > > > by
> > > > > counting the number of appointments between certain dates to get
> > monthly
> > > > > totals. I just don't know how to say that I want all dates
between
> > > > 01-Apr-05
> > > > > until say 30-Apr-05p and so on. Is it possible to do this - the
> > formula I
> > > > > was trying with was;
> > > > > =COUNT(IF('Master
> > > > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > > > But this isn't working!
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

In column A I have dates in the following format;
01-Apr-05 right through until the end of March 2006
On a different sheet in my workbook I want to summarise the information by
counting the number of appointments between certain dates to get monthly
totals. I just don't know how to say that I want all dates between 01-Apr-05
until say 30-Apr-05p and so on. Is it possible to do this - the formula I
was trying with was;
=COUNT(IF('Master
Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
But this isn't working!

Make sure that the sheet name referenced in the formula matches exactly
the sheet name in your file.

In article <[email protected]>,
Bugaglugs <[email protected]> wrote:

> Domenic
> Thanks - still puzzling over this as I copied and pasted the formula and am
> now getting #ref
>
> I'm sure this is me rather than you but I just can't understand it!
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> > Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
> >
> > or
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> > Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Bugaglugs <[email protected]> wrote:
> >
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the information
> > > by
> > > counting the number of appointments between certain dates to get monthly
> > > totals. I just don't know how to say that I want all dates between
> > > 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the formula
> > > I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> >

Thank you for all your help - have now found out the reason for my problems
when using all the suggested formulas....am VERY sorry to admit that I'd put
in the wrong date when trying it out so it was a simple case of user error on
my part. Thanks once again for all time taken from everyone!

"Bugaglugs" wrote:

> Domenic
> Thanks - still puzzling over this as I copied and pasted the formula and am
> now getting #ref
>
> I'm sure this is me rather than you but I just can't understand it!
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> > Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
> >
> > or
> >
> > =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> > Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Bugaglugs <[email protected]> wrote:
> >
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the information by
> > > counting the number of appointments between certain dates to get monthly
> > > totals. I just don't know how to say that I want all dates between 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> >

Domenic
Thanks - still puzzling over this as I copied and pasted the formula and am
now getting #ref

I'm sure this is me rather than you but I just can't understand it!

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
> Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))
>
> or
>
> =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
> Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))
>
> Hope this helps!
>
> In article <[email protected]>,
> Bugaglugs <[email protected]> wrote:
>
> > In column A I have dates in the following format;
> > 01-Apr-05 right through until the end of March 2006
> > On a different sheet in my workbook I want to summarise the information by
> > counting the number of appointments between certain dates to get monthly
> > totals. I just don't know how to say that I want all dates between 01-Apr-05
> > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > was trying with was;
> > =COUNT(IF('Master
> > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > But this isn't working!
>

Bob - spot on, I'm getting a '0' which I can't see the reason for!

"Bob Phillips" wrote:

> If they are dates, the format should not matter at all.
>
> What problems are you experiencing? Do you get 0 or an answer which you
> can't see the reason for?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
> > Bob - thanks for you prompt response - I'm still having problems, do you
> > think it's how I've formatted the cells with the dates in? I've got them
> so
> > that however the user inputs the date it comes up "01-Apr-05" format?
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> > >
> > > If you are just looking for a month, you can use
> > >
> > > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> > >
> > > or if there can be multiple yers, then
> > >
> > > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> > >
> > > or
> > >
> > > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Bugaglugs" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > In column A I have dates in the following format;
> > > > 01-Apr-05 right through until the end of March 2006
> > > > On a different sheet in my workbook I want to summarise the
> information
> > > by
> > > > counting the number of appointments between certain dates to get
> monthly
> > > > totals. I just don't know how to say that I want all dates between
> > > 01-Apr-05
> > > > until say 30-Apr-05p and so on. Is it possible to do this - the
> formula I
> > > > was trying with was;
> > > > =COUNT(IF('Master
> > > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > > But this isn't working!
> > > >
> > > >
> > >
> > >
> > >
>
>
>

If they are dates, the format should not matter at all.

What problems are you experiencing? Do you get 0 or an answer which you
can't see the reason for?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> Bob - thanks for you prompt response - I'm still having problems, do you
> think it's how I've formatted the cells with the dates in? I've got them
so
> that however the user inputs the date it comes up "01-Apr-05" format?
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> >
> > If you are just looking for a month, you can use
> >
> > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> >
> > or if there can be multiple yers, then
> >
> > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> >
> > or
> >
> > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> > > In column A I have dates in the following format;
> > > 01-Apr-05 right through until the end of March 2006
> > > On a different sheet in my workbook I want to summarise the
information
> > by
> > > counting the number of appointments between certain dates to get
monthly
> > > totals. I just don't know how to say that I want all dates between
> > 01-Apr-05
> > > until say 30-Apr-05p and so on. Is it possible to do this - the
formula I
> > > was trying with was;
> > > =COUNT(IF('Master
> > > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > > But this isn't working!
> > >
> > >
> >
> >
> >

Maybe, but we don't format dates like that in the UK, far better to use an
unambiguous way IMO, such as I showed or even

=SUMPRODUCT(--(A2:A1000>=--"2005-04-01")),--(A2:A1000<=--"2005-04-30"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Umlas" <[email protected]> wrote in message
news:%237RACb%[email protected]...
> This also works:
> =SUMPRODUCT(--(A2:A1000>="4/1/05"),--(A2:A1000<="4/20/05"))
>
> "Bob Phillips" <[email protected]> wrote in message
> news:eUW$3Y%[email protected]...
> >
=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
> >
> > If you are just looking for a month, you can use
> >
> > =SUMPRODUCT(--(MONTH(A2:A1000)=4))
> >
> > or if there can be multiple yers, then
> >
> > =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
> >
> > or
> >
> > =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bugaglugs" <[email protected]> wrote in message
> > news:[email protected]...
> >> In column A I have dates in the following format;
> >> 01-Apr-05 right through until the end of March 2006
> >> On a different sheet in my workbook I want to summarise the information
> > by
> >> counting the number of appointments between certain dates to get
monthly
> >> totals. I just don't know how to say that I want all dates between
> > 01-Apr-05
> >> until say 30-Apr-05p and so on. Is it possible to do this - the
formula
> >> I
> >> was trying with was;
> >> =COUNT(IF('Master
> >> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> >> But this isn't working!
> >>
> >>
> >
> >
>
>

Bob - thanks for you prompt response - I'm still having problems, do you
think it's how I've formatted the cells with the dates in? I've got them so
that however the user inputs the date it comes up "01-Apr-05" format?

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
>
> If you are just looking for a month, you can use
>
> =SUMPRODUCT(--(MONTH(A2:A1000)=4))
>
> or if there can be multiple yers, then
>
> =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
>
> or
>
> =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
> > In column A I have dates in the following format;
> > 01-Apr-05 right through until the end of March 2006
> > On a different sheet in my workbook I want to summarise the information
> by
> > counting the number of appointments between certain dates to get monthly
> > totals. I just don't know how to say that I want all dates between
> 01-Apr-05
> > until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> > was trying with was;
> > =COUNT(IF('Master
> > Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> > But this isn't working!
> >
> >
>
>
>

This also works:
=SUMPRODUCT(--(A2:A1000>="4/1/05"),--(A2:A1000<="4/20/05"))

"Bob Phillips" <[email protected]> wrote in message
news:eUW$3Y%[email protected]...
> =SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))
>
> If you are just looking for a month, you can use
>
> =SUMPRODUCT(--(MONTH(A2:A1000)=4))
>
> or if there can be multiple yers, then
>
> =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))
>
> or
>
> =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bugaglugs" <[email protected]> wrote in message
> news:[email protected]...
>> In column A I have dates in the following format;
>> 01-Apr-05 right through until the end of March 2006
>> On a different sheet in my workbook I want to summarise the information
> by
>> counting the number of appointments between certain dates to get monthly
>> totals. I just don't know how to say that I want all dates between
> 01-Apr-05
>> until say 30-Apr-05p and so on. Is it possible to do this - the formula
>> I
>> was trying with was;
>> =COUNT(IF('Master
>> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
>> But this isn't working!
>>
>>
>
>

Try...

=SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000>=DATE(2005,4,1)),--('Master
Sheet'!$A$2:$A$10000<=DATE(2005,4,30)))

or

=SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master
Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1))

Hope this helps!

In article <[email protected]>,
Bugaglugs <[email protected]> wrote:

> In column A I have dates in the following format;
> 01-Apr-05 right through until the end of March 2006
> On a different sheet in my workbook I want to summarise the information by
> counting the number of appointments between certain dates to get monthly
> totals. I just don't know how to say that I want all dates between 01-Apr-05
> until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> was trying with was;
> =COUNT(IF('Master
> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> But this isn't working!

=SUMPRODUCT(--(A2:A1000>=DATE(2005,04,01)),--(A2:A1000<=DATE(2005,04,20))

If you are just looking for a month, you can use

=SUMPRODUCT(--(MONTH(A2:A1000)=4))

or if there can be multiple yers, then

=SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4))

or

=SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bugaglugs" <[email protected]> wrote in message
news:[email protected]...
> In column A I have dates in the following format;
> 01-Apr-05 right through until the end of March 2006
> On a different sheet in my workbook I want to summarise the information
by
> counting the number of appointments between certain dates to get monthly
> totals. I just don't know how to say that I want all dates between
01-Apr-05
> until say 30-Apr-05p and so on. Is it possible to do this - the formula I
> was trying with was;
> =COUNT(IF('Master
> Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0))
> But this isn't working!
>
>