Add numbers if cell fill is a certain color; How To?

[Kirk Bubul]

Being old, I keep a spreadsheet of all medical events and their
eventual payments to medical providers. This year we have one
major category of outgo with numerous events. The amounts I'd
like to single out and sum are scattered throughout the rows of
Column G.

Can I use a unique fill color for all the expense amount cells
that I'd like to sum and then do a function that would add
together only the amounts in these colored cells? If so, please
tell me the formula.

Thanks in advance for your help.

 

[Claus Busch]

Hi Kirk,

Am Fri, 15 Feb 2013 09:33:06 -0600 schrieb Kirk Bubul:

Being old, I keep a spreadsheet of all medical events and their
eventual payments to medical providers. This year we have one
major category of outgo with numerous events. The amounts I'd
like to single out and sum are scattered throughout the rows of
Column G.

Can I use a unique fill color for all the expense amount cells
that I'd like to sum and then do a function that would add
together only the amounts in these colored cells? If so, please
tell me the formula.

you can add numbers by value, e.g. add all numbers greater 50:
=SUMIF(G2:G100,">50")
You also can highlight the numbers you want to add, then filter by color
and sum with:
=SUBTOTAL(9,G2:G100)


Regards
Claus Busch

 

[Gord Dibben]

Easy enough to calculate like count, sum etc. if using VBA and a User
Defined Function

Are you up for something like that?


Gord

 

[Kirk Bubul]

Unfortunately, the last programming I did was in a Fortran IV
class in 1971. Sorry, Gord. I need to be spoonfed a formula.

 

[Gord Dibben]

You want to keep track of the sum of cells that are scattered
throughout a worksheet.

Easist way to do this is include the entire range of cells on the
sheet and pick out just those of a particular color.

1. Open your workbook.
2. Hit Alt + F11 to open Visual Basic Editor
3. Left-click on your workbook name to select it
4. On upper menu hit Insert>Module
5. Copy this code into that module

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'You can call this function from a worksheet cell with a formula like
'=SUMBYCOLOR(A1:A10,3,FALSE)

Dim rng As Range
Dim OK As Boolean

Application.Volatile True
For Each rng In InRange.Cells
If OfText = True Then
OK = (rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(rng.Value) Then
SumByColor = SumByColor + rng.Value
End If
Next rng

End Function


6. Alt + Q to return to your Excel window.................save the
workbook.

7. Color the cells in column G to be summed yellow or a color of your
choice, as long as you know the color index number............CI for
yellow is 6

8. In any empty cell enter =SumByColor(G1:G1000,6) Make the
range large enough to cover future yellow cells.

This sums only the yellow cells.


Hope this helps. Gord

 

[Kirk Bubul]

You want to keep track of the sum of cells that are scattered
throughout a worksheet.

Easist way to do this is include the entire range of cells on the
sheet and pick out just those of a particular color.

1. Open your workbook.
2. Hit Alt + F11 to open Visual Basic Editor
3. Left-click on your workbook name to select it
4. On upper menu hit Insert>Module
5. Copy this code into that module

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'You can call this function from a worksheet cell with a formula like
'=SUMBYCOLOR(A1:A10,3,FALSE)

Dim rng As Range
Dim OK As Boolean

Application.Volatile True
For Each rng In InRange.Cells
If OfText = True Then
OK = (rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(rng.Value) Then
SumByColor = SumByColor + rng.Value
End If
Next rng

End Function


6. Alt + Q to return to your Excel window.................save the
workbook.

7. Color the cells in column G to be summed yellow or a color of your
choice, as long as you know the color index number............CI for
yellow is 6

8. In any empty cell enter =SumByColor(G1:G1000,6) Make the
range large enough to cover future yellow cells.

This sums only the yellow cells.


Hope this helps. Gord

It helps tremendously. Thanks you very much. I'll try to
implement it over the weekend.