Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
Easy enough to calculate like count, sum etc. if using VBA and a User
Defined Function Are you up for something like that? Gord On Fri, 15 Feb 2013 09:33:06 -0600, Kirk Bubul wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
Unfortunately, the last programming I did was in a Fortran IV
class in 1971. Sorry, Gord. I need to be spoonfed a formula. On Sat, 16 Feb 2013 14:17:57 -0800, Gord Dibben wrote: Easy enough to calculate like count, sum etc. if using VBA and a User Defined Function Are you up for something like that? Gord On Fri, 15 Feb 2013 09:33:06 -0600, Kirk Bubul wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
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 InsertModule 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 On Sun, 17 Feb 2013 06:07:56 -0600, Kirk Bubul wrote: Unfortunately, the last programming I did was in a Fortran IV class in 1971. Sorry, Gord. I need to be spoonfed a formula. On Sat, 16 Feb 2013 14:17:57 -0800, Gord Dibben wrote: Easy enough to calculate like count, sum etc. if using VBA and a User Defined Function Are you up for something like that? Gord On Fri, 15 Feb 2013 09:33:06 -0600, Kirk Bubul wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add numbers if cell fill is a certain color; How To?
On Fri, 22 Feb 2013 11:39:36 -0800, Gord Dibben
wrote: 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 InsertModule 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i auto fill cell color based on other cells' color? | Excel Programming | |||
How can I sum numbers that have a gray color-fill in cell? | Excel Discussion (Misc queries) | |||
Average numbers by fill color | Excel Discussion (Misc queries) | |||
Match TextBox Back Color to Cell Fill Color | Excel Programming | |||
change fill color of a range of cells based on color of a cell? | Excel Programming |