SUMPRODUCT Help

[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

 

[Glenn]

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

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

There is probably text somewhere in G1001:G10000.

 

[Eduardo]

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

 

[RoadKill]

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.

 

[RoadKill]

Eduardo, I realize that aspect as well but it gives the error message even
when adjusting all the B's and D's.

 

[T. Valko]

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)

 

[RoadKill]

That worked perfect. Thanks

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!