How do you autosum cells of the same color in microsoft excel?

Question

How do you autosum cells of the same color in microsoft excel?

Answer 1

There is no way to do that within Excel itself. You would need to use a VBA macro and that, depending on what you are trying to do, may be a tad cumbersome.



Here is a macro that, when activated, will ask for a cell reference and for a background colorindex number. Then the sum of the value in all cells with that background color will be placed in the cell you select.



It tabulates values in Column A based on cell interior color and places the sum in the cell you select. If you want to use a different column, change the 'A' in Line 3 of the macro from 'A1' and 'A' to your column letter.



I have only included a few color index codes in the inputbox. You can use any number from 1 to 56. Here is a link to a complete index if you wish to view them:



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



Open the workbook.



Copy this macro to the clipboard



Sub SumByColor()

Dim rng As Range

Set rng = Range("A1:" & Range("A" & _

ActiveSheet.Rows.Count). End(xlUp).Address)

CellSelect = InputBox("Enter a cell reference number")

selcolor = InputBox("Enter a color index to sum." & _

Chr(10) & Chr(10) & "Red: 3" & Chr(10) & _

"Lt Turquoise: 34" & Chr(10) & _

"Lt Yellow: 36" & Chr(10) & _

"Lt Blue: 37" & Chr(10) & "Lt Green: 35")

rng.Select

For Each cell In Selection

If cell.Interior.ColorIndex = Val(selcolor) Then

SumTtl = SumTtl + ActiveCell.Value

End If

Next

[A1].Select

If SumTtl = 0 Then

MsgBox "No cells with that background color."

Else

Range(CellSelect).Value = SumTtl

End If

End Sub



Press ALT + F11



Insert > Module



Paste the macro into the module space to the right.



Close back to Excel



Go to Tools > Macro > Macros



Highlight this macro if it is not already highlighted.



Click 'Options'



Select a letter to be used as a keyboard shortcut.



Close back to Excel.



Press CTRL + Your letter to run the macro.

Answer 2

I don't think you can just catagorize and make a formula for excel to summ all colored cells. You would actually have to go and and hold the control button while you click on each one with your mouse.

Answer 3

You can't. Excel doesn't take cell color into consideration in formulas. Sorry.

Answer 4

Excel Sum By Color

Answer 5

You can use a simple macro to do this. The following looks in column A for cells of a particular color and sums them in a cell in Col C: It creates variables (red yel blu) which are equal to the background colors of cells C1, C2, and C3, respectively. Then it creates 3 additonal variables (sred syel sblu) for the sum of each color category of cell that appears in Column A. It loops through column A and keeps the sum up to date every time you move the cursor. Each time, it sticks the values of sred syel and sblu in the appropriate cells, C1, C2, and C3. You can use whatever colors you want. If you have more than 3, just add them in. If your columns are different than mine, just edit the code. Note that in VBA, a cell (for instance D2) is referenced by its row number and then its column number-- cells(2,4).



To put this in your worksheet, just right click on the Sheet tab label at the bottom, click View Code, and paste this in (make whatever edits are necessary to suit your purposes). Close the code window, and you're done. Also, note that the underscores in the code are simply used as line extensions in VBA (for continuing a line on the next line when it's too long--only necessary here because Yahoo Answers screws it up otherwise).



Private Sub Worksheet_SelectionChange(ByVal Target _

As Range)



Dim red As Double

Dim yel As Double

Dim blu As Double



red = Cells(1, 3).Interior.ColorIndex

yel = Cells(2, 3).Interior.ColorIndex

blu = Cells(3, 3).Interior.ColorIndex



Dim sred As Double

Dim syel As Double

Dim sblu As Double



sred = 0

syel = 0

sblu = 0



r = 1

Do

If Cells(r, 1).Interior.ColorIndex = red Then sred = _

sred + Cells(r, 1)

If Cells(r, 1).Interior.ColorIndex = yel Then syel = _

syel + Cells(r, 1)

If Cells(r, 1).Interior.ColorIndex = blu Then sblu = _

sblu + Cells(r, 1)

r = r + 1

Loop Until Cells(r, 1) = ""



Cells(1, 3) = sred

Cells(2, 3) = syel

Cells(3, 3) = sblu



End Sub