Sumproduct trouble

[starryeyed]

I've tried, but I can't get it to work...

This is my formula
=SUMPRODUCT(--MONTH(Registrations!$K$3:$K$5000)=--MONTH(Helpdesk!$F$1),--Registrations!$K$3:$K$5000)

In Helpdesk!F1 I have 1/11/05
In registrariotns!K3:K5000 there's a list of dates, a few of which contain dates in November.

I've tried a countif, and I've tried the sumproduct, but both give the answer as 0 instead of 3. What have I done wrong?

 

[Legacy 51064]

=SUMPRODUCT((MONTH(Registrations!$K$3:$K$5000)=MONTH(Helpdesk!$F$1))*(Registrations!$K$3:$K$5000))

 

[starryeyed]

Thanks, but that gives me 270653!

I have exactly 7 dates which fit the criteria.

 

[Domenic]

Try...

=SUMPRODUCT(--(Registrations!$K$3:$K$5000<>""),--(MONTH(Registrations!$K$3:$K$5000)=MONTH(Helpdesk!$F$1)))

If you want to set your criteria based on the month and year, try the following...

=SUMPRODUCT(--(Registrations!$K$3:$K$5000-DAY(Registrations!$K$3:$K$5000)+1=DATE(YEAR(Helpdesk!F1),MONTH(Helpdesk!$F$1),1)))

Hope this helps!

 

[starryeyed]

Try...

=SUMPRODUCT(--(Registrations!$K$3:$K$5000<>""),--(MONTH(Registrations!$K$3:$K$5000)=MONTH(Helpdesk!$F$1)))

Brilliant! Thank you It's been doing head in all day lol