SUMPRODUCT Producing VALUE error

  • Thread starter Thread starter Brian Ferris
  • Start date Start date
B

Brian Ferris

Hi,

I have used sumproduct for a thousand times and somehow
this time am obtaining a VALUE error !!!

The formula is as follows:

=SUMPRODUCT((Data!$B$3:$B$9922=$B4)*(Data!$G$3:$G$9922=G$2)
*(Data!$G$3:$G$9922))

Can anyone help as to why i keep getting this error message

Thanks in advance,
Brian
 
The array arguments must have the same dimensions. If
they do not, SUMPRODUCT returns the #VALUE! error value.
See if your row numbers are the same in each.
 
Brian said:
Hi,

I have used sumproduct for a thousand times and somehow
this time am obtaining a VALUE error !!!

The formula is as follows:

=SUMPRODUCT((Data!$B$3:$B$9922=$B4)*(Data!$G$3:$G$9922=G$2)
*(Data!$G$3:$G$9922))

Can anyone help as to why i keep getting this error message

Thanks in advance,
Brian
Click to expand...

My guess: You should have B2 instead of B4 in the first condition.

Dave
dvt at psu dot edu
 
Only thing I can think of from the information you've posted is that
you haven't pressed the correct key combination for an array function.
Activate the cell in edit mode and press Ctrl-Shift-Enter
simultaneously and see if that works.
Click to expand...

OP's SUMPRODUCT formula doesn't need to be entered as an array formula.
If that is not it, you'd have to look in your source table and check
for correct formatting there. An issue may be that a range such as
Data!$B$3:$B$9922 contains labels in stead of values.
Click to expand...

Likely this is the problem.
...

Debugging time. What do the following formulas return?

If B4 is text,

=(COUNTA(Data!$B$3:$B$9922)-COUNT(Data!$B$3:$B$9922))=ROWS(Data!$B$3:$B$9922)

If B4 is numeric,

=COUNT(Data!$B$3:$B$9922)=ROWS(Data!$B$3:$B$9922)

Also,

=COUNTIF(Data!$B$3:$B$9922,B4)

and

=COUNTIF(Data!$G$3:$G$9922,G2)

Finally, given the conditional expressions in your formula, you'd get the same
result from

=COUNTIF(Data!$B$3:$B$9922,$B4)*G$2
 
Back
Top