| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Help
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
RoadKill wrote:
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
Eduardo, I realize that aspect as well but it gives the error message even
when adjusting all the B's and D's. "Eduardo" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
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) -- Biff Microsoft Excel MVP "RoadKill" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
That worked perfect. Thanks
"T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RoadKill" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RoadKill" wrote in message ... That worked perfect. Thanks "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RoadKill" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Can I use SUMPRODUCT for this? | Excel Worksheet Functions | |||
| how to use sumproduct | Excel Worksheet Functions | |||
| sumproduct | Excel Worksheet Functions | |||
Hybrid Mode
