|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 12th, 2009, 05:35 PM
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
May 12th, 2009, 05:40 PM
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
May 12th, 2009, 05:48 PM
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
May 12th, 2009, 06:24 PM
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
|
|
#5
May 12th, 2009, 06:25 PM
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
|
|
#6
May 12th, 2009, 07:31 PM
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
May 12th, 2009, 08:46 PM
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
May 12th, 2009, 10:46 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
