excel sumproduct value error

[cstang]

Hello,

Can anyone pls help me?
The below formula return #VALUE! error.
=SUMPRODUCT(($H4=$B$52:$B$2000)*(J$3=$E$52:$F$2000)*($G$52:$G$2000))

But when I change to:
=SUMPRODUCT(($H4=$B$52:$B$500)*(J$3=$E$52:$F$500)*($G$52:$G$500))
It works

What is the problem with the formula?
Thanks in advance.
Cstang

 

[Bob Phillips]

That would suggest that one of the cells between rows 500 and 2000 contains
a #VALUE!, in other words a data error

 

[cstang]

That would suggest that one of the cells between rows 500 and 2000
contains a #VALUE!, in other words a data error

--

HTH

Bob

Thank you Bob
But there is no data error in between the rows...

Cstang

 

[Bob Phillips]

Maybe you have text in column G. Check it with

=COUNTA($G$52:$G$2000)

 

[Dave Peterson]

Check your other post, too.

Hello,

Can anyone pls help me?
The below formula return #VALUE! error.
=SUMPRODUCT(($H4=$B$52:$B$2000)*(J$3=$E$52:$F$2000)*($G$52:$G$2000))

But when I change to:
=SUMPRODUCT(($H4=$B$52:$B$500)*(J$3=$E$52:$F$500)*($G$52:$G$500))
It works

What is the problem with the formula?
Thanks in advance.
Cstang

 

[Stan Brown]

Hello,

Can anyone pls help me?
The below formula return #VALUE! error.
=SUMPRODUCT(($H4=$B$52:$B$2000)*(J$3=$E$52:$F$2000)*($G$52:$G$2000))

But when I change to:
=SUMPRODUCT(($H4=$B$52:$B$500)*(J$3=$E$52:$F$500)*($G$52:$G$500))
It works

What is the problem with the formula?

I doubt there's anything wrong with the formula. Instead, I suspect
somewhere between row 501 and row 2000 you have a #VALUE error.

 

[Bob Phillips]

Why didn't I think of that!

--

HTH

Bob

I doubt there's anything wrong with the formula. Instead, I suspect
somewhere between row 501 and row 2000 you have a #VALUE error.