Sumif based on strikethrough

[ingmar67]

Hello,

I want to do a SUMIF based on the text attribute "strikethrough" (i.e.
do not sum those that are strikethrough). I found one based on colors,
but that is not quite the same....Anybody who can help?

Ingmar

 

[Nikos Yannacopoulos]

As far as I know, you can only do this through VB code.
Here's an example piece of code that will run through a
range of starting in A1 (you can change accordingly), find
the last populated cell down, perform the sumif anp place
the result in the next cell down:

Sub sum_non_strikethrough()
sum1 = 0
Range("A1").Select
Selection.End(xlDown).Select
lr = ActiveCell.Row
Range("A1").Select

For i = 1 To lr
If Selection.Font.strikethrough = False Then
sum1 = sum1 + ActiveCell.Value2
End If
ActiveCell.Offset(1, 0).Select
Next
ActiveCell.Value2 = sum1
End Sub


Nikos Y.

-----Original Message-----
Hello,

I want to do a SUMIF based on the text

attribute "strikethrough" (i.e.

 

[Peter Atherton]

Ingmar

A quick reply, click on the Sheet TAb and select ViewCode
and paste this in.

Sub Test()
For Each c In Selection
c.Select
If ActiveCell.Font.Strikethrough = True Then
Tot = Tot + c
End If
Next c
MsgBox "StrikeThrough total = " & Tot
End Sub

Peter

-----Original Message-----
Hello,

I want to do a SUMIF based on the text

attribute "strikethrough" (i.e.

 

[Peter Atherton]

Ingmar

I have written a function to do the same trick. Paste this
into the sheet module as before and use like any function.
i.e. =Strikesum(Any range here)

Function StrikeSum(n)
For Each i In n
If i.Cells.Font.Strikethrough = True Then
myTot = i.Value + myTot
End If
Next i
StrikeSum = myTot
End Function

Regards
Peter

 

[JMay]

Is your function code below to be pasted into the Sheet module vs a Standard
module?
I've got it going in s Standard Module, but when I select or deselect the
strikethrough
on a given cell my Function is not Recalcing. I'm missing something here//
TIA,

 

[Chip Pearson]

The code belongs in a standard code module, not the code module associated
with the worksheet. When you change the format of a cell (bold,
strikethrough, color, etc), Excel does not see this as a change of data, and
therefore does not recalculate. There is no ideal way around this
limitation. You could put 'Application.Volatile True' as the first line of
code in the procedure, which will cause Excel to recalculate the function
when any calculation is done, but this still doesn't force a calculation
when the font is changed.

 

[JMay]

Chip:
Thanks for the response..
JMay

The code belongs in a standard code module, not the code module associated
with the worksheet. When you change the format of a cell (bold,
strikethrough, color, etc), Excel does not see this as a change of data, and
therefore does not recalculate. There is no ideal way around this
limitation. You could put 'Application.Volatile True' as the first line of
code in the procedure, which will cause Excel to recalculate the function
when any calculation is done, but this still doesn't force a calculation
when the font is changed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com (e-mail address removed)