Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I have a cost worksheet where I need to calculate a sum based on 2 different
critera that are entered onto 2 seperate worksheets. One worksheet is an order entry worksheet where an order # and value are entered, the second worksheet is an invoice worksheet where the invoice # and invoice value are entered, the order # is also entered here for cross referencing, there is also a delivery code on each invoice line, the delivery code may apply to several invoices. What I need is a formula to sum up the total order value on sheet #1 for corresponding order #'s on sheet #2 that all have matching delivery codes. The current formula I am tryin is: =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) The result is the total order value for all orders on the order sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
3 immediate points. The ranges must be the same. You can't use B:B, it nees
to be B2:B2001 and your arguments should be separated by commas. Also, your whole sale orders don't need the parens or -- because it isn't a criteria. =SUMPRODUCT(--(Invoices!F2:F2001=A2),'Wholesale Orders'!F2:F2001,--(Invoices!B2:B20011)) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "ocuhcs" wrote: I have a cost worksheet where I need to calculate a sum based on 2 different critera that are entered onto 2 seperate worksheets. One worksheet is an order entry worksheet where an order # and value are entered, the second worksheet is an invoice worksheet where the invoice # and invoice value are entered, the order # is also entered here for cross referencing, there is also a delivery code on each invoice line, the delivery code may apply to several invoices. What I need is a formula to sum up the total order value on sheet #1 for corresponding order #'s on sheet #2 that all have matching delivery codes. The current formula I am tryin is: =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) The result is the total order value for all orders on the order sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I think you may have missed a few commas from your formula.
=SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) is equivalent to =SUMPRODUCT((Invoices!F2:F2001=A2)+('Wholesale Orders'!F2:F2001))+(Invoices!B:B1) You may have intended =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B:B1)) but in that case you would have tried to ask for a whole column for column B whereas you've asked for rows 2 to 2001 for column F on the 2 sheets. You need the same dimensions for all the arrays in your SUMPRODUCT. Perhaps you may have wanted =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B2:B20011)) ? But what are you looking for in your --('Wholesale Orders'!F2:F2001)) test? Is it just looking for a non-zero value? -- David Biddulph "ocuhcs" wrote in message ... I have a cost worksheet where I need to calculate a sum based on 2 different critera that are entered onto 2 seperate worksheets. One worksheet is an order entry worksheet where an order # and value are entered, the second worksheet is an invoice worksheet where the invoice # and invoice value are entered, the order # is also entered here for cross referencing, there is also a delivery code on each invoice line, the delivery code may apply to several invoices. What I need is a formula to sum up the total order value on sheet #1 for corresponding order #'s on sheet #2 that all have matching delivery codes. The current formula I am tryin is: =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) The result is the total order value for all orders on the order sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
The column 'Wholesale Orders'!F2:F2001 contains the order value that I want
summed up. I think the trick here that I am missing is that this value is only summed up when (Invoices!B2:B2000) is equal to 'Wholesale Orders'!B2:B2000 and Invoices!F2:F20011 "David Biddulph" wrote: I think you may have missed a few commas from your formula. =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) is equivalent to =SUMPRODUCT((Invoices!F2:F2001=A2)+('Wholesale Orders'!F2:F2001))+(Invoices!B:B1) You may have intended =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B:B1)) but in that case you would have tried to ask for a whole column for column B whereas you've asked for rows 2 to 2001 for column F on the 2 sheets. You need the same dimensions for all the arrays in your SUMPRODUCT. Perhaps you may have wanted =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B2:B20011)) ? But what are you looking for in your --('Wholesale Orders'!F2:F2001)) test? Is it just looking for a non-zero value? -- David Biddulph "ocuhcs" wrote in message ... I have a cost worksheet where I need to calculate a sum based on 2 different critera that are entered onto 2 seperate worksheets. One worksheet is an order entry worksheet where an order # and value are entered, the second worksheet is an invoice worksheet where the invoice # and invoice value are entered, the order # is also entered here for cross referencing, there is also a delivery code on each invoice line, the delivery code may apply to several invoices. What I need is a formula to sum up the total order value on sheet #1 for corresponding order #'s on sheet #2 that all have matching delivery codes. The current formula I am tryin is: =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) The result is the total order value for all orders on the order sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
So those are the conditions which you should include in your SUMPRODUCT,
along with your 'Wholesale Orders'!F2:F2001 value (which doesn't need the double unary minus if you are not trying to convert from Boolean to a number). But as we said earlier, your arrays need to be of the same length. You can't go from 2 to 2001 in some parts and 2 to 2000 in other parts of the same formula. -- David Biddulph "ocuhcs" wrote in message ... The column 'Wholesale Orders'!F2:F2001 contains the order value that I want summed up. I think the trick here that I am missing is that this value is only summed up when (Invoices!B2:B2000) is equal to 'Wholesale Orders'!B2:B2000 and Invoices!F2:F20011 "David Biddulph" wrote: I think you may have missed a few commas from your formula. =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) is equivalent to =SUMPRODUCT((Invoices!F2:F2001=A2)+('Wholesale Orders'!F2:F2001))+(Invoices!B:B1) You may have intended =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B:B1)) but in that case you would have tried to ask for a whole column for column B whereas you've asked for rows 2 to 2001 for column F on the 2 sheets. You need the same dimensions for all the arrays in your SUMPRODUCT. Perhaps you may have wanted =SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale Orders'!F2:F2001),--(Invoices!B2:B20011)) ? But what are you looking for in your --('Wholesale Orders'!F2:F2001)) test? Is it just looking for a non-zero value? -- David Biddulph "ocuhcs" wrote in message ... I have a cost worksheet where I need to calculate a sum based on 2 different critera that are entered onto 2 seperate worksheets. One worksheet is an order entry worksheet where an order # and value are entered, the second worksheet is an invoice worksheet where the invoice # and invoice value are entered, the order # is also entered here for cross referencing, there is also a delivery code on each invoice line, the delivery code may apply to several invoices. What I need is a formula to sum up the total order value on sheet #1 for corresponding order #'s on sheet #2 that all have matching delivery codes. The current formula I am tryin is: =SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale Orders'!F2:F2001))--(Invoices!B:B1) The result is the total order value for all orders on the order sheet. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I tried the following formula that contians all the correct conditions, but
comes up with an incorrect sum as the result. =SUMPRODUCT(--(Invoices!B2:B2000='Wholesale Orders'!B2:B2000))--(Invoices!F2:F2000=A2)--('Wholesale Orders'!F2:F2000) "David Biddulph" wrote: So those are the conditions which you should include in your SUMPRODUCT, along with your 'Wholesale Orders'!F2:F2001 value (which doesn't need the double unary minus if you are not trying to convert from Boolean to a number). But as we said earlier, your arrays need to be of the same length. You can't go from 2 to 2001 in some parts and 2 to 2000 in other parts of the same formula. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
You do not have the correct syntax - you've missed a few commas (or
your regional settings might use semi-colons), as well as a bracket at the end, and you have an extra one in the middle. Try it like this: =SUMPRODUCT(--(Invoices!B2:B2000='Wholesale Orders'!B2:B2000),-- (Invoices!F2:F2000=A2),--('Wholesale Orders'!F2:F2000)) Hope this helps. Pete On Jul 30, 2:27*pm, ocuhcs wrote: I tried the following formula that contians all the correct conditions, but comes up with an incorrect sum as the result. =SUMPRODUCT(--(Invoices!B2:B2000='Wholesale Orders'!B2:B2000))--(Invoices!F2:F2000=A2)--('Wholesale Orders'!F2:F2000) "David Biddulph" wrote: So those are the conditions which you should include in your SUMPRODUCT, along with your 'Wholesale Orders'!F2:F2001 value (which doesn't need the double unary minus if you are not trying to convert from Boolean to a number). But as we said earlier, your arrays need to be of the same length. *You can't go from 2 to 2001 in some parts and 2 to 2000 in other parts of the same formula.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
You mustn't have read the part of my earlier message
] (the content of which you've snipped) where I said you seemed to have missed a few commas and got the syntax wrong (and where I suggested what you might have been trying to do). And you've also presumably not read the bit below where I said that the 'Wholesale Orders'F... bit doesn't need the double unary minus if you are not trying to convert from Boolean to a number. -- David Biddulph "ocuhcs" wrote in message ... I tried the following formula that contians all the correct conditions, but comes up with an incorrect sum as the result. =SUMPRODUCT(--(Invoices!B2:B2000='Wholesale Orders'!B2:B2000))--(Invoices!F2:F2000=A2)--('Wholesale Orders'!F2:F2000) "David Biddulph" wrote: So those are the conditions which you should include in your SUMPRODUCT, along with your 'Wholesale Orders'!F2:F2001 value (which doesn't need the double unary minus if you are not trying to convert from Boolean to a number). But as we said earlier, your arrays need to be of the same length. You can't go from 2 to 2001 in some parts and 2 to 2000 in other parts of the same formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct | Excel Worksheet Functions | |||
Can I use SUMPRODUCT for this? | Excel Worksheet Functions |