| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use of "if" based on text in another column
Is there a way to use an 'if' statement in a column that is using an "=b1*c1"
statement to not preform the function if the text in column A starts with a "Q" We have a sheet that the first column has job #'s that start with an "X" and Quote #'s that start with a "Q", I don't want the price of the quote numbers to be included in the total for the column. Thanks Gary D. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of "if" based on text in another column
=IF(LEFT(A1,1)="Q","",B1*C1) I think this will do what you are asking. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php...o&userid=19838 View this thread: http://www.excelforum.com/showthread...hreadid=495692 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of "if" based on text in another column
If you want the column total to only include values where Col_B begins with
an "X", try something like this: =SUMIF(B1:B10,"X*",C1:C10) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Gary D." wrote: Is there a way to use an 'if' statement in a column that is using an "=b1*c1" statement to not preform the function if the text in column A starts with a "Q" We have a sheet that the first column has job #'s that start with an "X" and Quote #'s that start with a "Q", I don't want the price of the quote numbers to be included in the total for the column. Thanks Gary D. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of "if" based on text in another column
Thanks, This did what I need
Gary D. "goober" wrote: =IF(LEFT(A1,1)="Q","",B1*C1) I think this will do what you are asking. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php...o&userid=19838 View this thread: http://www.excelforum.com/showthread...hreadid=495692 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of if across worksheets (was use of "if" based on text in another column)
Hi Ron, or anyone else
I am trying to do a similar exercise, but drill down through a number of worksheets. I want to only sum those values which appear on a worksheet where A1 has the value "stat". aprstat and marstat are sheetnames at the start and end of the ranges to be summed I have found =SUM(IF((aprstat:marstat!A1="stat"),aprstat:marsta t!B6)) gives #name?, and =SUMIF(aprstat:marstat!A1,"=stat",aprstat:marstat! B6) gives #value!. I can get the equivalent sum command to work across the sheets =SUM(aprstat:marstat!B6) . Can anyone help? Rikki -- "Ron Coderre" wrote in message ... If you want the column total to only include values where Col_B begins with an "X", try something like this: =SUMIF(B1:B10,"X*",C1:C10) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Gary D." wrote: Is there a way to use an 'if' statement in a column that is using an "=b1*c1" statement to not preform the function if the text in column A starts with a "Q" We have a sheet that the first column has job #'s that start with an "X" and Quote #'s that start with a "Q", I don't want the price of the quote numbers to be included in the total for the column. Thanks Gary D. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of if across worksheets (was use of "if" based on text in another column)
It's not possible to use the same method when using sumif across multiple
sheets. 1. you need to put the names of ALL sheets that you want to be included, not just the first and the last, so if you have for instance 6 sheets you have to put all 6 names in a range like H1:H6 or something then use that range in your formula as follows =SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H6&"'!$A$1"),"st at",INDIRECT("'"&H1:H6&"'! $B$6"))) so put your sheet names in a range and replace H1:H6 with that range -- Regards, Peo Sjoblom "Rikki Ward" wrote in message ... Hi Ron, or anyone else I am trying to do a similar exercise, but drill down through a number of worksheets. I want to only sum those values which appear on a worksheet where A1 has the value "stat". aprstat and marstat are sheetnames at the start and end of the ranges to be summed I have found =SUM(IF((aprstat:marstat!A1="stat"),aprstat:marsta t!B6)) gives #name?, and =SUMIF(aprstat:marstat!A1,"=stat",aprstat:marstat! B6) gives #value!. I can get the equivalent sum command to work across the sheets =SUM(aprstat:marstat!B6) . Can anyone help? Rikki -- "Ron Coderre" wrote in message ... If you want the column total to only include values where Col_B begins with an "X", try something like this: =SUMIF(B1:B10,"X*",C1:C10) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Gary D." wrote: Is there a way to use an 'if' statement in a column that is using an "=b1*c1" statement to not preform the function if the text in column A starts with a "Q" We have a sheet that the first column has job #'s that start with an "X" and Quote #'s that start with a "Q", I don't want the price of the quote numbers to be included in the total for the column. Thanks Gary D. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of if across worksheets (was use of "if" based on text in another column)
Thanks Peo
I'll have a play with this tomorrow. Have a good Christmas break Rikki -- "Peo Sjoblom" wrote in message ... It's not possible to use the same method when using sumif across multiple sheets. 1. you need to put the names of ALL sheets that you want to be included, not just the first and the last, so if you have for instance 6 sheets you have to put all 6 names in a range like H1:H6 or something then use that range in your formula as follows =SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H6&"'!$A$1"),"st at",INDIRECT("'"&H1:H6&"'! $B$6"))) so put your sheet names in a range and replace H1:H6 with that range -- Regards, Peo Sjoblom "Rikki Ward" wrote in message ... Hi Ron, or anyone else I am trying to do a similar exercise, but drill down through a number of worksheets. I want to only sum those values which appear on a worksheet where A1 has the value "stat". aprstat and marstat are sheetnames at the start and end of the ranges to be summed I have found =SUM(IF((aprstat:marstat!A1="stat"),aprstat:marsta t!B6)) gives #name?, and =SUMIF(aprstat:marstat!A1,"=stat",aprstat:marstat! B6) gives #value!. I can get the equivalent sum command to work across the sheets =SUM(aprstat:marstat!B6) . Can anyone help? Rikki -- "Ron Coderre" wrote in message ... If you want the column total to only include values where Col_B begins with an "X", try something like this: =SUMIF(B1:B10,"X*",C1:C10) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Gary D." wrote: Is there a way to use an 'if' statement in a column that is using an "=b1*c1" statement to not preform the function if the text in column A starts with a "Q" We have a sheet that the first column has job #'s that start with an "X" and Quote #'s that start with a "Q", I don't want the price of the quote numbers to be included in the total for the column. Thanks Gary D. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sum a column based on another column | Excel Discussion (Misc queries) | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Linear Mode
