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
 
Ad

Advertisements

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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top