how to get the sum of numbers same color in excel?

Philippines
June 16, 2012 2:51am CST
So to start with I have numbers all put in the same column. Now, the cells in the column have different colors say orange, blue, green. My question is how do I get the sum of the numbers that are in the cells within the column that belong to the same background or font color? I know that I should have combined the numbers so that what I only have to do then is write the formula =SUM(A2:A16) e.g. but no they are interspersed with other numbers of different color and it takes a lot of time to write all when writing the formula. What should I do here to get the sum of the numbers that have the same color that are separated by different-colored numbers? How do I get the sum of the numbers in the column excluding that of the different colored numbers?
1 person likes this
2 responses
dollar3235
@dollar3235 (2062)
• India
16 Jun 12
Hi Cowboyofhell, Good question to ask..you will find the answer here: http://www.ozgrid.com/forum/showthread.php?t=40042 You can also try this one: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm Do let me know if you still have any difficulty..
1 person likes this
cowboyofhell
@cowboyofhell (3063)
• Philippines
16 Jun 12
Thanks for the links dude.. I've found a workaround with the SUMIF function. This will do for now while I learn the VBA option. Geez, wish I had learned this all in high school. Anyway, this seemed like killing two birds with one stone. thanks again.
1 person likes this
Just an Ordinary Owl?
@owlwings (43915)
• Cambridge, England
16 Jun 12
Nice solution to an unusual problem, dollar3235! It's true to say that one can learn something new every day! I think I would have tried to use SUMIF, too. Cowboyofhell, in the first example that dollar3235 gave, note that the questioner (chos10) gets an error with the code because they haven't declared the function at the beginning - scroll down to see dangelor's correction, which should work.
1 person likes this
cowboyofhell
@cowboyofhell (3063)
• Philippines
16 Jun 12
It didn't. Not sure why. Second link is the correct procedure owlwings.. worked just as nice as I wanted. Yay! Paste the code below at VBA... [i]Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function[/i]
1 person likes this
jamunaaprajapati
@jamunaaprajapati (23)
• India
26 Mar 13
There is no criteria for foreground and background color, however you can write a macro where it will be possible.