Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
I am looking to add a conditional sum to a workbook. The trick is
that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
There is no difference if the other workbook is open, if it is closed then
you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000) note that you cannot use A:A unless you are using Excel 2007, you need to specify the range in previous versions Make sure the workbook is opened when you apply the formula than Excel will take care of paths and names etc when/if you close the other workbook -- Regards, Peo Sjoblom "jlclyde" wrote in message ... On Nov 27, 10:34 am, "Peo Sjoblom" wrote: There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
On Nov 27, 11:02 am, "Peo Sjoblom" wrote:
=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book 1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000) note that you cannot use A:A unless you are using Excel 2007, you need to specify the range in previous versions Make sure the workbook is opened when you apply the formula than Excel will take care of paths and names etc when/if you close the other workbook -- Regards, Peo Sjoblom "jlclyde" wrote in message ... On Nov 27, 10:34 am, "Peo Sjoblom" wrote: There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay- Hide quoted text - - Show quoted text - Again, I am sure that I am not coming across clearly. I need to sum row c in workbook 1 based on if the information in A and B are equal to X and Y. These will be totaled on the next workbook. Thanks, Jay |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
On Nov 27, 12:03 pm, jlclyde wrote:
On Nov 27, 11:02 am, "Peo Sjoblom" wrote: =SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book 1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000) note that you cannot use A:A unless you are using Excel 2007, you need to specify the range in previous versions Make sure the workbook is opened when you apply the formula than Excel will take care of paths and names etc when/if you close the other workbook -- Regards, Peo Sjoblom "jlclyde" wrote in message ... On Nov 27, 10:34 am, "Peo Sjoblom" wrote: There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay- Hide quoted text - - Show quoted text - Again, I am sure that I am not coming across clearly. I need to sum row c in workbook 1 based on if the information in A and B are equal to X and Y. These will be totaled on the next workbook. Thanks, Jay- Hide quoted text - - Show quoted text - Here is the actual information In workbook 2 I have a list of dates in A. At the top of the work sheet is the Operators name and employee #. In column B I want excel to lookup the date, the employee name and number and return the sum of all rows that are equal to these from workbook 1. So if a row matches date, employee name and number it will sum quantity column. Thanks, Jay |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
That's not what you said, you said
you wanted to sum B based on A and C =SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book 1.xls]Sheet1'!$B$1:$B$10000="Y"),'[Work book 1.xls]Sheet1'!$C$1:$C$10000) -- Regards, Peo Sjoblom note that you cannot use A:A unless you are using Excel 2007, you need to specify the range in previous versions Make sure the workbook is opened when you apply the formula than Excel will take care of paths and names etc when/if you close the other workbook -- Regards, Peo Sjoblom "jlclyde" wrote in message ... On Nov 27, 10:34 am, "Peo Sjoblom" wrote: There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay- Hide quoted text - - Show quoted text - Again, I am sure that I am not coming across clearly. I need to sum row c in workbook 1 based on if the information in A and B are equal to X and Y. These will be totaled on the next workbook. Thanks, Jay |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
On Nov 27, 12:20 pm, "Peo Sjoblom" wrote:
That's not what you said, you said you wanted to sum B based on A and C =SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book 1.xls]Sheet1'!$B$1:$B$10000="Y"),'[Work book 1.xls]Sheet1'!$C$1:$C$10000) -- Regards, Peo Sjoblom note that you cannot use A:A unless you are using Excel 2007, you need to specify the range in previous versions Make sure the workbook is opened when you apply the formula than Excel will take care of paths and names etc when/if you close the other workbook -- Regards, Peo Sjoblom "jlclyde" wrote in message ... On Nov 27, 10:34 am, "Peo Sjoblom" wrote: There is no difference if the other workbook is open, if it is closed then you can use either a SUM array formula or SUMPRODUCT (better) SUMIF however does not work if the other workbook is closed nor does DSUM -- Regards, Peo Sjoblom "jlclyde" wrote in message ... I am looking to add a conditional sum to a workbook. The trick is that the information that i want to sum is another workbook. Is this possible or do i need to write some code in VBA? Thanks, Jay- Hide quoted text - - Show quoted text - I shoudl have been more descriptive of what I am looking for. I want to set up a conditional sum from one workbook to antoher. There are multiple conditions that I need it to meet. Here is an example This is in work book 1 Column A is Names Column B is output Column C is dates This is in workbook 2 (nothing yet) I am trying to get if Column A in workbook 1 = X and Column C in workbook 1 = Y then Sum Column B in workbook 1. Is this possible? Jay- Hide quoted text - - Show quoted text - Again, I am sure that I am not coming across clearly. I need to sum row c in workbook 1 based on if the information in A and B are equal to X and Y. These will be totaled on the next workbook. Thanks, Jay- Hide quoted text - - Show quoted text - I just want to say that this works great. I have never thought of using the Sumproduct this way. I am sorry that it took me so long to get it. Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
Help - Conditional Sum!! | Excel Discussion (Misc queries) | |||
Conditional DAY | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Conditional Sum by Row | Excel Worksheet Functions |