I only want to add up?!

[SugarPlum]

I have a spreadsheet with columns of numbers. I want to create totals at the bottom of each column.

However, I want to exclude from the sum certain rows which I have highlighted in a particular colour.

Is it possible to do this without manually specifying the cells which I want to exclude? Or is there a property in Excel where I can specify an attribute such as column colour?

Answers on a postcard to ...

 

[Hasit]

I am not sure whether you can specify not including cells based on a colour. Do the cells you wish to ignore have a common (or several common) properties?

There are lots of solutions to your dilemna if they do, such as conditional sum or conditional formatting.

Others may have a way to select on the color using VB, but I can't think of one formulaicly (is that a word?).

 

[logius]

Well, you can do this using VBA (a macro), but I don't know of any easy way to do this using regular Excel formulas. Just paste this in your workbook as a module (hit ALT+F11 from excel, right-click the workbook and choose Insert>Module, copy the code, and save the document). From then on you'll be able to use it as you would a reglar formula. It requires that you specify a cell with the color you want to sum and the targeted range.

Function SumColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult

   iCol = rColor.Font.ColorIndex
   For Each rCell In rSumRange
     If rCell.Font.ColorIndex = iCol Then
       vResult = WorksheetFunction.Sum(rCell) + vResult
     End If
   Next rCell

   SumColor = vResult
End Function

You would type in something like "=SumColor(A1, A1:A9)" in the formula bar. The original code (I hardly modified it) can be found at

http://www.microsoftexceltraining.com/Sum.html

. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com