Sumproduct error

  • Thread starter Thread starter Constantin
  • Start date Start date
C

Constantin

Hi!

I have a formula that work fine on a computer, but on other computer (same
software installed) it return #VALUE. Formula is:
=SUMPRODUCT(--(Export!$G$2:$G$100>C9);--(Export!$G$2:$G$100<D9);--(Export!$D$2:$D$100))

Can anybody help?
Thanks
 
Yes, both. And I don't understand why is working on a computer and not on
other.
Same software installed, same settings.
 
Do you have any errors in those ranges?

Do you have any non-numeric data in Export!D2:D100?

Maybe trying this version:

=SUMPRODUCT(--(Export!$G$2:$G$100>C9);
--(Export!$G$2:$G$100<D9);
(Export!$D$2:$D$100))

I'm not sure why you wanted those double minuses in front of the last portion.
 
It is probably an error in the data.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hello!

There is no non-numeric data in Export!D2:D100. And no, I have no errors in
those range.

I've tried the same formula on Excel2007 and it work too.

If you think it helps I can send you the file.

Thanks.
 
No thanks to the offer of the workbook.

But I'd look again for non-numeric data in that range. Maybe you have hidden
rows (filtered???).

Or maybe you have errors in one of those ranges.
 
Constantin said:
Hi!

I have a formula that work fine on a computer, but on other computer (same
software installed) it return #VALUE. Formula is:
=SUMPRODUCT(--(Export!$G$2:$G$100>C9);--(Export!$G$2:$G$100<D9);--(Export!$D$2:$D$100))
Click to expand...

There appears to be no valid reason for a different behavoir
in two similar computers, except that computers are sometimes
a strange mixture of hardware and software.

Did you try this?
=SUMPRODUCT((Export!$G$2:$G$100>C9)*
(Export!$G$2:$G$100<D9)*
(Export!$D$2:$D$100))
Bruno
 
Sorry for late answer.

It works when I put
=SUMPRODUCT(--(Export!$G$2:$G$100>C9);
--(Export!$G$2:$G$100<D9);
(Export!$D$2:$D$100)).

Thanks a lot!
Best regards,
Constantin.
 
That means that you have some non-numeric data in D2:D100. You may want to
double check to see if that's what you intended.
Sorry for late answer.

It works when I put
=SUMPRODUCT(--(Export!$G$2:$G$100>C9);
--(Export!$G$2:$G$100<D9);
(Export!$D$2:$D$100)).

Thanks a lot!
Best regards,
Constantin.
Click to expand...
 
Back
Top