Hi Mandy

Sumproduct will not take whole columns as ranges.
Use Sheet2!$J$1:$J$10000 or whatever length would be appropriate to the
size of your database, up to 65535

--
Regards

Roger Govier


"Mandy" wrote in message
...
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