I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

You will need a User Defined Function for this.

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

Usage is.......=SumNoStrike(A1:A20)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown above.


Gord Dibben Excel MVP

On Thu, 14 Dec 2006 11:22:01 -0800, baklava
wrote:

I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

Gord Dibben MS Excel MVP

You're working against Excel, instead of with it.....Excel's native functions
can't "see" cell formats.

Instead, flag the numbers with a "helper" column where you put an "x" to
mark the values to skip. Then you can just use the SUMIF function.

Example:
To add only the items in A1:A10 where the corresponding itmes in Col_B do
NOT equal "x"
=SUMIF(B1:B10,"<x",A1:A10)

(Otherwise, you'll need a custom VBA function to do what you're asking for.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"baklava" wrote:

I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

Thank you for the insight! The numbers were struck through bcz the policies
were cancelled and did not need to be included in the total revenue for that
year.

Ron, your explaination makes total sense and I've added it to my
spreadsheets. Thanks to all for the quick response!



"Ron Coderre" wrote:

You're working against Excel, instead of with it.....Excel's native functions
can't "see" cell formats.

Instead, flag the numbers with a "helper" column where you put an "x" to
mark the values to skip. Then you can just use the SUMIF function.

Example:
To add only the items in A1:A10 where the corresponding itmes in Col_B do
NOT equal "x"
=SUMIF(B1:B10,"<x",A1:A10)

(Otherwise, you'll need a custom VBA function to do what you're asking for.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"baklava" wrote:

I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

I don't think it's possible to do this directly. SUMIF (and other functions)
work on conditions - strikethrough is simply a formatting tool and doesn't
affect the condition of the cell contents.

Perhaps you could include a neighbouring column to hold some
symbol/number/text as well as striking through the original cell. This value
could then be used by SUMIF to determine which cells to incorporate. If you
found this additional column presentationally intrusive, you could make it
invisible via conditional formatting or by hiding the column, though it's
then difficult to visually check that you've set all the correct trigger
points.

"baklava" wrote:

I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

*Why* are the numbers struck through?

That's the logic you should use to write a formula to do this. You should
not base calculations on cell formats. Thoroughly test the UDF and you'll
see what I mean.

Biff

"baklava" wrote in message
...
I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!

Good point Biff.

If strikethrough by CF the SumNoStrike will fail.


Gord

On Thu, 14 Dec 2006 16:54:10 -0500, "T. Valko" wrote:

*Why* are the numbers struck through?

That's the logic you should use to write a formula to do this. You should
not base calculations on cell formats. Thoroughly test the UDF and you'll
see what I mean.

Biff

"baklava" wrote in message
...
I have a column of numbers that I want to total; however, some of the rows
have the number struck through - these are the numbers I DO NOT want to
include in the total.

=sumif(a1:a20, and here is where I get stuck. How do I tell excel to sum
all the numbers in the range except those that have strike through.

Or is there a different formula to use?

Thanx!


Gord Dibben MS Excel MVP