SUMPRODUCT using months

[LoopKid]

I have a worksheet with column N as "JoinDate," with dates listed as days of
the year, and column M as "MemberType:"

Column M Column N
MemberType JoinDate
Individual 2/14/2003
Individual 3/25/2003
Business 1/27/2003
Corporate 7/2/2003

I need help finding a formula to determine how many of each member type
joined during each month. I think it might be a SUMPRODUCT formula, but I
don't know how to specify an argument to find all dates between to other
dates (all days greater than or equal to Feb. 1 and less than or equal to
Feb. 28). Can anybody help me with this??

[Ragdyer]

Assuming all the dates in Column N are "true" dates, enter the number of the
month you're looking for in P1 (1=Jan, 2=Feb ... etc.),and the member type
in O1, and try this formula:

=SUMPRODUCT((M2:M50=O1)*(MONTH(N2:N50)=P1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"LoopKid" <LoopKid@discussions.microsoft.com> wrote in message
news:7BAF651E-A5C2-4E05-86C1-ED122680CEAF@microsoft.com...
> I have a worksheet with column N as "JoinDate," with dates listed as days
of
> the year, and column M as "MemberType:"
>
> Column M Column N
> MemberType JoinDate
> Individual 2/14/2003
> Individual 3/25/2003
> Business 1/27/2003
> Corporate 7/2/2003
>
> I need help finding a formula to determine how many of each member type
> joined during each month. I think it might be a SUMPRODUCT formula, but I
> don't know how to specify an argument to find all dates between to other
> dates (all days greater than or equal to Feb. 1 and less than or equal to
> Feb. 28). Can anybody help me with this??

[LoopKid]

That worked wonderfully, but I'm still missing one thing! I need to be able
to specify the year as well. Right now I'm getting all Individual members
who joined in January of any year, and I need to find how many joined in Jan
'02, Feb '02, Jan '03, etc.
This is closer than I've gotten in a week though! Thanks much for the input!

LK

"Ragdyer" wrote:

> Assuming all the dates in Column N are "true" dates, enter the number of the
> month you're looking for in P1 (1=Jan, 2=Feb ... etc.),and the member type
> in O1, and try this formula:
>
> =SUMPRODUCT((M2:M50=O1)*(MONTH(N2:N50)=P1))
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "LoopKid" <LoopKid@discussions.microsoft.com> wrote in message
> news:7BAF651E-A5C2-4E05-86C1-ED122680CEAF@microsoft.com...
> > I have a worksheet with column N as "JoinDate," with dates listed as days
> of
> > the year, and column M as "MemberType:"
> >
> > Column M Column N
> > MemberType JoinDate
> > Individual 2/14/2003
> > Individual 3/25/2003
> > Business 1/27/2003
> > Corporate 7/2/2003
> >
> > I need help finding a formula to determine how many of each member type
> > joined during each month. I think it might be a SUMPRODUCT formula, but I
> > don't know how to specify an argument to find all dates between to other
> > dates (all days greater than or equal to Feb. 1 and less than or equal to
> > Feb. 28). Can anybody help me with this??
>
>

[Ragdyer]

Then in P1 enter a true date, and try this formula:

=SUMPRODUCT((M2:M50=O1)*(TEXT(N2:N50,"mm/yy")=TEXT(P1,"mm/yy")))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"LoopKid" <LoopKid@discussions.microsoft.com> wrote in message
news:E2DFFC98-FEB8-4126-B52D-5C671104DC0C@microsoft.com...
> That worked wonderfully, but I'm still missing one thing! I need to be
able
> to specify the year as well. Right now I'm getting all Individual members
> who joined in January of any year, and I need to find how many joined in
Jan
> '02, Feb '02, Jan '03, etc.
> This is closer than I've gotten in a week though! Thanks much for the
input!
>
> LK
>
> "Ragdyer" wrote:
>
> > Assuming all the dates in Column N are "true" dates, enter the number of
the
> > month you're looking for in P1 (1=Jan, 2=Feb ... etc.),and the member
type
> > in O1, and try this formula:
> >
> > =SUMPRODUCT((M2:M50=O1)*(MONTH(N2:N50)=P1))
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> > "LoopKid" <LoopKid@discussions.microsoft.com> wrote in message
> > news:7BAF651E-A5C2-4E05-86C1-ED122680CEAF@microsoft.com...
> > > I have a worksheet with column N as "JoinDate," with dates listed as
days
> > of
> > > the year, and column M as "MemberType:"
> > >
> > > Column M Column N
> > > MemberType JoinDate
> > > Individual 2/14/2003
> > > Individual 3/25/2003
> > > Business 1/27/2003
> > > Corporate 7/2/2003
> > >
> > > I need help finding a formula to determine how many of each member
type
> > > joined during each month. I think it might be a SUMPRODUCT formula,
but I
> > > don't know how to specify an argument to find all dates between to
other
> > > dates (all days greater than or equal to Feb. 1 and less than or equal
to
> > > Feb. 28). Can anybody help me with this??
> >
> >