Two things, I believe.
First, my understanding of sumproduct is that you cannot use an entire
column reference. Instead of J:J, for instance, use J1:J2000; likewise for
the reference to columns D, B, M and Q.
Second, the comparison of the entire column B to the 6 cells in D5:I5 is
illogical; you've got to compare many cells to a single value or a group of
cells to an equal number of cells. If your cells are merged, the value is in
the first. So instead of D5:I5, just compare to D5.
Does that get you past #num?
--Bruce

"Mandy" wrote:

Hello Can anyone help me? The below formula keeps giving me #num as an answer
what am i doning wrong?

sumproduct(--(sheet2!J:J=sheet1!$C$2),--(sheet2!D:D="ELECTIVE"),--(Sheet!2B:B=sheet1!$D$5:$I5),--(Sheet2!M:M=sheet1!$B9),Sheet2!Q:Q)


The formaula is in sheet 1 and it needs to give me a total which is in sheet
2 col Q if certain cells match ie sheet1 c2 is a person's name and sheet 1
d5:i5 is a type of cost (these cells are merged so has only one type of cost
in them)

Hope I am clear

many thx

Mandy