IF Strikethrough

[lightbulb]

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!

 

[Luke M]

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.

 

[Gary''s Student]

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.

 

[CLR]

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")*(D22375="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

 

[Gord Dibben]

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