| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum
Hello,
Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
What version of Excel are you using? Unless you're using Excel 2007 you
*can't* use entire columns as range references. Try it like this: =SUMPRODUCT(--('[Lux Tenure 9.14.08.xls]Previous Week'!$A1:$A10="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C1:$C10) -- Biff Microsoft Excel MVP "Herb" wrote in message ... Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
Try this:
=Sumif(A:A,"X",C:C) Add your path since your description is confusing. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Herb" wrote in message ... Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
Herb,
When working with a SUMPRODUCT you cannot use Columns, you have to specify the range (i.e. A1:A10 instead of A:A). Secondly, when it does return the answer it is going to be placed in TRUE/FALSE instead of the 1/0 which you require. Placing a double negation will allow your data to come in as 1/0. Therefore, the code you would wish to use is such: =SUMPRODUCT(--($A1:$A10="x"),$C1:$C10) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "Herb" wrote: Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
Sorry, I didn't read to the end of your post where you used the path as an
example. Try this: =SUMIF('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A,"X",'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Try this: =Sumif(A:A,"X",C:C) Add your path since your description is confusing. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Herb" wrote in message ... Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
Of course, the WB must be open for the Sumif() function to work.
You already have solutions if that's not convenient. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Sorry, I didn't read to the end of your post where you used the path as an example. Try this: =SUMIF('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A,"X",'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Try this: =Sumif(A:A,"X",C:C) Add your path since your description is confusing. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Herb" wrote in message ... Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Sum
Thank you both so much for your help on this! I knew it was something simple
but I just couldn't get it. Thanks again! "Herb" wrote: Hello, Can someone please help with this formula. I have a worksheet called Template and another called Tenure 9.14.08 and the tab on that spreadsheet is called "Previous Week." On my Template spreadsheet I am trying to set up a formula that will look in Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it will give me a sum of Column C of all of the rows that have an X. For instance: A B C X Mary 12 George 2 X Bob 24 X Henry 8 I would like the formula to look at Column A and if there is an X add the data that is in column C. So my result would be 44. Hopefully this makes sense. I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C)) But it is oh so very, very wrong. Thanks for your help in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
