sumproduct

[Guest]

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="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

 

[Roger Govier]

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

 

[Guest]

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

 

[Don Guillett]

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