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

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

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

since sumproduct will not take entire columns you could define a name bb and
have it refer to b1:b65535 but not 65536

--
Don Guillett
SalesAid Software

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