Sumproduct trouble

starryeyed

Board Regular
Joined
Jun 22, 2005
Messages
221
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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
L

Legacy 51064

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

starryeyed

Board Regular
Joined
Jun 22, 2005
Messages
221
Thanks, but that gives me 270653!

I have exactly 7 dates which fit the criteria.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
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

Board Regular
Joined
Jun 22, 2005
Messages
221
Domenic said:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,432
Messages
5,572,072
Members
412,437
Latest member
PietTheijssen
Top