SUMPRODUCT Help

  • Thread starter Thread starter RoadKill
  • Start date Start date
R

RoadKill

Here is my formula:

=SUMPRODUCT(('5-1'!$B$2:$B$1000="John Doe")&('5-1'!$D$2:$D$1000="Product
One")&('5-1'!$D$2:$D$1000="Product Two")*('5-1'!$G$2:$G$1000)).

The addition is off, the total should only be 13 but is counting 43.

Second, when I change the range from G2:G1000 to what it should be
G2:G10000, it gives me a #value error message.

What am I missing?

Thank you
 
RoadKill said:
Here is my formula:

=SUMPRODUCT(('5-1'!$B$2:$B$1000="John Doe")&('5-1'!$D$2:$D$1000="Product
One")&('5-1'!$D$2:$D$1000="Product Two")*('5-1'!$G$2:$G$1000)).

The addition is off, the total should only be 13 but is counting 43.

Second, when I change the range from G2:G1000 to what it should be
G2:G10000, it gives me a #value error message.

What am I missing?

Thank you
Click to expand...


You should have "*" instead of "&".

There is probably text somewhere in G1001:G10000.
 
RoadKill said:
Here is my formula:

=SUMPRODUCT(('5-1'!$B$2:$B$1000="John Doe")&('5-1'!$D$2:$D$1000="Product
One")&('5-1'!$D$2:$D$1000="Product Two")*('5-1'!$G$2:$G$1000)).

The addition is off, the total should only be 13 but is counting 43.

Second, when I change the range from G2:G1000 to what it should be
G2:G10000, it gives me a #value error message.

What am I missing?

Thank you
Click to expand...


You should have "*" instead of "&".

There is probably text somewhere in G1001:G10000.
 
Hi,
regarding your 2nd question, all the ranges has to match so you need to
enter 10000 in B and D as well
1s question was answered Glenn
 
Hi,
regarding your 2nd question, all the ranges has to match so you need to
enter 10000 in B and D as well
1s question was answered Glenn
 
Sorry, I did have a typo, it is actually:

=SUMPRODUCT(('5-1'!$B$2:$B$1000="John Doe")*('5-1'!$D$2:$D$1000="Product
One")*('5-1'!$D$2:$D$1000="Product Two")*('5-1'!$G$2:$G$1000)).

But it doesn't work properly either way.
 
Sorry, I did have a typo, it is actually:

=SUMPRODUCT(('5-1'!$B$2:$B$1000="John Doe")*('5-1'!$D$2:$D$1000="Product
One")*('5-1'!$D$2:$D$1000="Product Two")*('5-1'!$G$2:$G$1000)).

But it doesn't work properly either way.
 
Eduardo, I realize that aspect as well but it gives the error message even
when adjusting all the B's and D's.
 
Eduardo, I realize that aspect as well but it gives the error message even
when adjusting all the B's and D's.
 
Try it like this (I left out the sheet name so be sure to add it):

=SUMPRODUCT(--($B$2:$B$1000="John Doe"),($D$2:$D$1000="Product
One")+($D$2:$D$1000="Product Two"),$G$2:$G$1000)
 
Try it like this (I left out the sheet name so be sure to add it):

=SUMPRODUCT(--($B$2:$B$1000="John Doe"),($D$2:$D$1000="Product
One")+($D$2:$D$1000="Product Two"),$G$2:$G$1000)
 
That worked perfect. Thanks

T. Valko said:
Try it like this (I left out the sheet name so be sure to add it):

=SUMPRODUCT(--($B$2:$B$1000="John Doe"),($D$2:$D$1000="Product
One")+($D$2:$D$1000="Product Two"),$G$2:$G$1000)
Click to expand...
 
That worked perfect. Thanks

T. Valko said:
Try it like this (I left out the sheet name so be sure to add it):

=SUMPRODUCT(--($B$2:$B$1000="John Doe"),($D$2:$D$1000="Product
One")+($D$2:$D$1000="Product Two"),$G$2:$G$1000)
Click to expand...
 
Back
Top