Re: How to count the number of times something occurs within a certain month

Thanks Roger,

It works great for all the months except for Jan.
I used the following formula for Jan and instead of returning 3 it returned
77.

=SUMPRODUCT(--(MONTH($B$1:$B$100)=1))

I'm not sure why, if you have any ideas please let me know.

Thanks,
Joyce

From: "Roger Govier" <[email protected]>
To: "Joyce" <[email protected]>
Subject: Re: How to count the number of times something occurs within a
certain month
Date: Tuesday, October 18, 2005 10:11 AM

Hi Joyce

One way
=SUMPRODUCT(--(MONTH($A$1:$A$100)=2)
for February
Change to 3 for March etc. or put the Month number required in a cell and
refer to the cell
=SUMPRODUCT(--(MONTH($A$1:$A$100)=B1)

Regards

Roger Govier


Joyce wrote:
> Hi,
>
> Does anyone have a suggestion on how I should do the following?
>
> I have a table column that contains dates like,
>
> 1 Feb 05
> 6 Feb 05
> 27 Feb 05
> 7 Mar 05
> 20 Mar 05
>
> I want to be able to count Feb = 3 and Mar = 2.
>
> Thanks!
> Joyce
>
>

Blanks and zeros will come up as "1" for the month() figure
if you have non dates if would use
=SUMPRODUCT(--(MONTH($B$1:$B$100)=1),--($B$1:$B$100>36525))
if all of your dates are later than 1999.
"Joyce" wrote:

> Thanks Roger,
>
> It works great for all the months except for Jan.
> I used the following formula for Jan and instead of returning 3 it returned
> 77.
>
> =SUMPRODUCT(--(MONTH($B$1:$B$100)=1))
>
> I'm not sure why, if you have any ideas please let me know.
>
> Thanks,
> Joyce
>
> From: "Roger Govier" <[email protected]>
> To: "Joyce" <[email protected]>
> Subject: Re: How to count the number of times something occurs within a
> certain month
> Date: Tuesday, October 18, 2005 10:11 AM
>
> Hi Joyce
>
> One way
> =SUMPRODUCT(--(MONTH($A$1:$A$100)=2)
> for February
> Change to 3 for March etc. or put the Month number required in a cell and
> refer to the cell
> =SUMPRODUCT(--(MONTH($A$1:$A$100)=B1)
>
> Regards
>
> Roger Govier
>
>
> Joyce wrote:
> > Hi,
> >
> > Does anyone have a suggestion on how I should do the following?
> >
> > I have a table column that contains dates like,
> >
> > 1 Feb 05
> > 6 Feb 05
> > 27 Feb 05
> > 7 Mar 05
> > 20 Mar 05
> >
> > I want to be able to count Feb = 3 and Mar = 2.
> >
> > Thanks!
> > Joyce
> >
> >
>
>

Hi Joyce

I guess some of the cells in the range were blank. Excel would have treated
them as being 01 Jan 1900, and therefore counted them in with the month 1 total

We can add another condition to the formula
=SUMPRODUCT(--(MONTH($B$1:$B$100)=1,--($B$1:$B$100<>""))
which will test for null values.

Regards

Roger Govier


Joyce wrote:
> Thanks Roger,
>
> It works great for all the months except for Jan.
> I used the following formula for Jan and instead of returning 3 it returned
> 77.
>
> =SUMPRODUCT(--(MONTH($B$1:$B$100)=1))
>
> I'm not sure why, if you have any ideas please let me know.
>
> Thanks,
> Joyce
>
> From: "Roger Govier" <[email protected]>
> To: "Joyce" <[email protected]>
> Subject: Re: How to count the number of times something occurs within a
> certain month
> Date: Tuesday, October 18, 2005 10:11 AM
>
> Hi Joyce
>
> One way
> =SUMPRODUCT(--(MONTH($A$1:$A$100)=2)
> for February
> Change to 3 for March etc. or put the Month number required in a cell and
> refer to the cell
> =SUMPRODUCT(--(MONTH($A$1:$A$100)=B1)
>
> Regards
>
> Roger Govier
>
>
> Joyce wrote:
>
>>Hi,
>>
>>Does anyone have a suggestion on how I should do the following?
>>
>>I have a table column that contains dates like,
>>
>>1 Feb 05
>>6 Feb 05
>>27 Feb 05
>>7 Mar 05
>>20 Mar 05
>>
>>I want to be able to count Feb = 3 and Mar = 2.
>>
>>Thanks!
>>Joyce
>>
>>
>
>