# another sumproduct with #value error...

M

#### Maya

Hello,

I'm trying to compare 7 columns in two worksheets, containing text, numbers
and dates. I found here a very helpful formula with sumproduct that woked on
my trial spreadsheet, but returns a #value error on the real thing.

The formula is:

=SUMPRODUCT(--(Sheet2!\$A\$1:\$A10=A1),--(Sheet2!\$B\$1:\$B10=B1),--(Sheet2!\$C\$1:\$C10=C1),--(Sheet2!\$D\$1:\$D10=D1),--(Sheet2!\$E\$1:\$E34=E1),--(Sheet2!\$F\$1:\$F10=F1),Sheet2!G\$1:\$G\$10)

Any ideas what I'm doing wrong?

Thanks,
Maya

J

#### Jacob Skaria

Try the below. The range should be same

=SUMPRODUCT(--(Sheet2!\$A\$1:\$A10=A1),--(Sheet2!\$B\$1:\$B10=B1)
--(Sheet2!\$C\$1:\$C10=C1),--(Sheet2!\$D\$1:\$D10=D1),--(Sheet2!\$E\$1:\$E10=E1),--(Sheet2!\$F\$1:\$F10=F1),Sheet2!G\$1:\$G\$10)

If this post helps click Yes

E

#### Eduardo

Hi,
Range should be the same in each component of the formula try

=SUMPRODUCT(--(Sheet2!\$A\$1:\$A34=A1),--(Sheet2!\$B\$1:\$B34=B1),--(Sheet2!\$C\$1:\$C34=C1),--(Sheet2!\$D\$1:\$D34=D1),--(Sheet2!\$E\$1:\$E34=E1),--(Sheet2!\$F\$1:\$F34=F1),Sheet2!G\$1:\$G\$34)

M

#### Maya

Hi Jacob,

Thanks for this!

I corrected the range to be the same and now I get a 0 instead of the value
in column G. My row 1 was copied from Sheet2 row 1 so I'm positive they do
match. any more thoughts?

Many thanks
Maya

M

#### Max

... I get a 0 instead of the value in column G.
My row 1 was copied from Sheet2 row 1 so I'm positive they do match.

Could be that the nums in col G are text nums
Try an "+0" to col G to coerce it to sum correctly, viz make it:
... ,Sheet2!G\$1:\$G\$10+0)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

M

#### Maya

you're a star Max - it works perfectly now!

Thanks to Jacob, Max and Eduardo for taking the time to help!

Maya
www.concern-universal.org