another sumproduct with #value error...

[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

 

[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

 

[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)

 

[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

 

[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
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[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