Sumif based on strikethrough

I

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
 
N

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
Click to expand...
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
 
P

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
Click to expand...
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
 
P

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
-----Original Message-----
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
Click to expand...
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
.
Click to expand...
 
J

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,

Peter Atherton said:
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
-----Original Message-----
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
Click to expand...
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
.
Click to expand...
Click to expand...
 
C

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.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com [email protected]


JMay said:
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,

Peter Atherton said:
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
-----Original Message-----
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.
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

.
Click to expand...
Click to expand...
Click to expand...
 
J

JMay

Chip:
Thanks for the response..
JMay

Chip Pearson said:
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 [email protected]


JMay said:
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,

Peter Atherton said:
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
-----Original Message-----
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.
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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

.
Click to expand...
Click to expand...
Click to expand...
 
Top