SUMPRODUCT using months

[Guest]

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))

 

[Guest]

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]

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

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