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!

Answer: IF Strikethrough
 

Yes, there is a way to exclude cells with strikethrough lines from a formula. You can use the following formula:

1. SUMIFS function: This function adds up the values in the range G22:G375 that meet certain criteria.
2. Range: The range C22:C375 is used as the criteria range.
3. Criteria: The criteria for the range C22:C375 is set to "<" which means that the cells in this range should not be blank.
4. NOT function: This function returns the opposite of a logical value. In this case, it returns TRUE if the cell does not have a strikethrough line.
5. FIND function: This function searches for a specific character or text within a cell. In this case, it searches for the strikethrough line character "¯" within the format of the cell.
6. CELL function: This function returns information about the formatting, location, or contents of a cell.

Here's how this formula works:

By combining these functions, the formula will only sum up the values in the range G22:G375 if the corresponding cell in the range C22:C375 is not blank and does not have a strikethrough line. This way, you can keep the formulas in G22:G375 and still exclude certain cells from the calculation based on their formatting.

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!

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!

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:D375="IN")*(G22: G375))+SUMPRODUCT((C22:C375="DEF")*(D22:D375="IN") *(G22:G375))+SUMPRODUCT((C22:C375="GHI")*(D22:D375 ="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!

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!