|
| 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 15th, 2009, 01:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
IF Strikethrough
Is there a way to have a formula not include a cell that the text has
strikethrough lines? What I need is if $C$22:$C$375 = abc or = def or = ghi then sum G22:G375 unless strikethgrough has been added to the text by formatting of the cell. It's the only way I could think of for certain cells to sometimes not be added up (if the job is sent out rather than done in-plant) without deleteing the formulas in G22:G375 Any Suggestions? Thanks! 
|
|
#2
May 15th, 2009, 02:31 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Strikethrough
Instead of doing a strikethough (which, by the way, Excel formulas can not
detect) you could have the formula search for the reason there is a strike though. Is there a cell that calls out "sent out" or perhaps an "X" somewhere indicating this? Note that if there is, not only could you build the formula that way, but you can use conditional formatting to create the strikethrough effect on your other cells. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "lightbulb" wrote: Is there a way to have a formula not include a cell that the text has strikethrough lines? What I need is if $C$22:$C$375 = abc or = def or = ghi then sum G22:G375 unless strikethgrough has been added to the text by formatting of the cell. It's the only way I could think of for certain cells to sometimes not be added up (if the job is sent out rather than done in-plant) without deleteing the formulas in G22:G375 Any Suggestions? Thanks!
|
|
#3
May 15th, 2009, 02:34 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Strikethrough
Usually you would need macros to do this, but in this case you can avoid
macros if you use a helper column. Say A1 thru A20 contain numbers and Z1 thru Z20 contain 1. Click on A1 and: Format Conditional Formatting... Formula Is =Z1=0 and pick strikethrough format. In another cell enter: =SUMPRODUCT(A1:A20,Z1:Z20) If you zero any cells from Z1 thru Z20, the equivalent cells in column A will appear strikethrough and the SUMPRODUCT formula will reflect that. -- Gary''s Student - gsnu200853 "lightbulb" wrote: Is there a way to have a formula not include a cell that the text has strikethrough lines? What I need is if $C$22:$C$375 = abc or = def or = ghi then sum G22:G375 unless strikethgrough has been added to the text by formatting of the cell. It's the only way I could think of for certain cells to sometimes not be added up (if the job is sent out rather than done in-plant) without deleteing the formulas in G22:G375 Any Suggestions? Thanks!
|
|
#4
May 15th, 2009, 02:48 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Strikethrough
If you have a column that contains the "in-house, out-of-house" distinction,
then you could use the SUMPRODUCT formula, something like: =SUMPRODUCT((C22:C375="ABC")*(D22  375="IN")*(G22: G375))+SUMPRODUCT((C22:C375="DEF")*(D22375="IN") *(G22:G375))+SUMPRODUCT((C22:C375="GHI")*(D22375 ="IN")*(G22:G375))Crude, but works, Vaya con Dios, Chuck, CABGx3 "lightbulb" wrote: Is there a way to have a formula not include a cell that the text has strikethrough lines? What I need is if $C$22:$C$375 = abc or = def or = ghi then sum G22:G375 unless strikethgrough has been added to the text by formatting of the cell. It's the only way I could think of for certain cells to sometimes not be added up (if the job is sent out rather than done in-plant) without deleteing the formulas in G22:G375 Any Suggestions? Thanks!
|
|
#5
May 16th, 2009, 05:03 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Strikethrough
This UDF will exclude cells with strikethrough.
Public Function SumNoStrike(rngSumRange As Range) As Single Dim rngCell As Range For Each rngCell In rngSumRange If IsNumeric(rngCell.Value) Then If rngCell.Font.Strikethrough = False Then SumNoStrike = SumNoStrike + rngCell.Value End If End If Next rngCell End Function Gord Dibben MS Excel MVP On Fri, 15 May 2009 05:52:03 -0700, lightbulb wrote: Is there a way to have a formula not include a cell that the text has strikethrough lines? What I need is if $C$22:$C$375 = abc or = def or = ghi then sum G22:G375 unless strikethgrough has been added to the text by formatting of the cell. It's the only way I could think of for certain cells to sometimes not be added up (if the job is sent out rather than done in-plant) without deleteing the formulas in G22:G375 Any Suggestions? Thanks!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
