software

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=quot;ELECTIVEquot;),--(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

MandyHi 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 Govierquot;Mandyquot; gt; wrote in message
...
gt; Hello Can anyone help me? The below formula keeps giving me #num as an
gt; answer
gt; what am i doning wrong?
gt;
gt; sumproduct(--(sheet2!J:J=sheet1!$C$2),--(sheet2!D=quot;ELECTIVEquot;),--(Sheet!2B:B=sheet1!$D$5:$I5),--(Sheet2!M:M=sheet1!$B9),Sheet2!Q:Q)
gt;
gt;
gt; The formaula is in sheet 1 and it needs to give me a total which is in
gt; sheet
gt; 2 col Q if certain cells match ie sheet1 c2 is a person's name and
gt; sheet 1
gt; d5:i5 is a type of cost (these cells are merged so has only one type
gt; of cost
gt; in them)
gt;
gt; Hope I am clear
gt;
gt; many thx
gt;
gt; Mandy
gt;
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

quot;Mandyquot; wrote:

gt; Hello Can anyone help me? The below formula keeps giving me #num as an answer
gt; what am i doning wrong?
gt;
gt; sumproduct(--(sheet2!J:J=sheet1!$C$2),--(sheet2!D=quot;ELECTIVEquot;),--(Sheet!2B:B=sheet1!$D$5:$I5),--(Sheet2!M:M=sheet1!$B9),Sheet2!Q:Q)
gt;
gt;
gt; The formaula is in sheet 1 and it needs to give me a total which is in sheet
gt; 2 col Q if certain cells match ie sheet1 c2 is a person's name and sheet 1
gt; d5:i5 is a type of cost (these cells are merged so has only one type of cost
gt; in them)
gt;
gt; Hope I am clear
gt;
gt; many thx
gt;
gt; Mandy
gt;

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

quot;bpeltzerquot; gt; wrote in message
...
gt; Two things, I believe.
gt; First, my understanding of sumproduct is that you cannot use an entire
gt; column reference. Instead of J:J, for instance, use J1:J2000; likewise
gt; for
gt; the reference to columns D, B, M and Q.
gt; Second, the comparison of the entire column B to the 6 cells in D5:I5 is
gt; illogical; you've got to compare many cells to a single value or a group
gt; of
gt; cells to an equal number of cells. If your cells are merged, the value is
gt; in
gt; the first. So instead of D5:I5, just compare to D5.
gt; Does that get you past #num?
gt; --Bruce
gt;
gt; quot;Mandyquot; wrote:
gt;
gt;gt; Hello Can anyone help me? The below formula keeps giving me #num as an
gt;gt; answer
gt;gt; what am i doning wrong?
gt;gt;
gt;gt; sumproduct(--(sheet2!J:J=sheet1!$C$2),--(sheet2!D=quot;ELECTIVEquot;),--(Sheet!2B:B=sheet1!$D$5:$I5),--(Sheet2!M:M=sheet1!$B9),Sheet2!Q:Q)
gt;gt;
gt;gt;
gt;gt; The formaula is in sheet 1 and it needs to give me a total which is in
gt;gt; sheet
gt;gt; 2 col Q if certain cells match ie sheet1 c2 is a person's name and sheet
gt;gt; 1
gt;gt; d5:i5 is a type of cost (these cells are merged so has only one type of
gt;gt; cost
gt;gt; in them)
gt;gt;
gt;gt; Hope I am clear
gt;gt;
gt;gt; many thx
gt;gt;
gt;gt; Mandy
gt;gt;