another sumproduct with #value error...

  • Thread starter Thread starter Maya
  • Start date Start date
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
 
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
 
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)
 
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
 
... 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.
Click to expand...

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
---
 
Back
Top