Excluding "Strikethrough" numbers from a summation

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Feb 7, 2005

• #2

As far as I know you're going to need a macro of some sort to detect the strikethrough formatting.

However, you can try a function macro. You can then use it just like a normal function.

Put the following code in a module in your workbook:

Public Function STRIKESUM(ByVal myRange As Range)
Dim cell As Range, x As Integer

For Each cell In myRange
If cell.Font.Strikethrough = False Then x = x + cell
Next cell
STRIKESUM = x
End Function

For example, let's say your numbers are in A1:A10. You can use =STRIKESUM(A1:A10) as your formula, and it should return the sum of all of the numbers in that range that do not have a strike.

One downside of this is that the formula will not update automatically if changes are made to A1:A10--you will need to press F9 to recalculate.

 

Upvote 0

N

NextYearSox

Active Member

Joined

Oct 24, 2003

Messages

287

• Feb 7, 2005

• #3

I'm not familiar with Function Macro's - so I just copied and pasted your macro. But had no luck. When I enter the data, and use the =strikesum command, EXCEL returns: #VALUE. Am I missing something?

 

Upvote 0

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Feb 7, 2005

• #4

Where did you paste the macro?

 

Upvote 0

N

NextYearSox

Active Member

Joined

Oct 24, 2003

Messages

287

• Feb 7, 2005

• #5

In a module

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Feb 7, 2005

• #6

Are you actually entering a range for strikesum?

eg

=strikesum(A1:A100)

 

Upvote 0

N

NextYearSox

Active Member

Joined

Oct 24, 2003

Messages

287

• Feb 7, 2005

• #7

I assume I do this in the EXCEL spreadsheet? (i.e., in cell K17, my formula is =STRIKESUM(K4:K16)

 

Upvote 0

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Feb 7, 2005

• #8

Yep, that should do it. Did you put it in a separate module? You don't want to put it in any of the Sheet or ThisWorkbook modules.

 

Upvote 0

N

NextYearSox

Active Member

Joined

Oct 24, 2003

Messages

287

• Feb 7, 2005

• #9

yup - that's exactly what I did. My data is in cells k4:k16, my formula is in k17 [=STRIKESUM(K4:K16)], and my macro is as follows:



Public Function STRIKESUM(ByVal myRange As Range)
Dim cell As Range, x As Integer

For Each cell In myRange
If cell.Font.Strikethrough = False Then x = x + cell
Next cell
STRIKESUM = x
End Function

 

Upvote 0

N

NextYearSox

Active Member

Joined

Oct 24, 2003

Messages

287

• Feb 7, 2005

• #10

I figured it out (I think). My numbers were too big - so I Dim'd x as double, which seems to have solved the problem.

Thanks everyone!

 

Upvote 0