In solving Excel sum colored cells problem without macro, it is best to think of a process that will let Excel recognise the cells you want to sum.
Suppose you want to highlight cells in column B and have cell E2 sum up those coloured cells. Essentially we want to link coloured cells to the value of 1 using CELL() function, and sum up those cells if CELL() function returns a 1.
Sum Cells By Color
To sum cells by color, firstly add a column to check which cells are colored.
In cell C1, put the formula
and drag the formula down to the end.
CELL() function with “color” argument lets Excel inform you if a cell has colour formatting for negative values. This is the first step to Excel sum colored cells.
We will create such formats in a blank cell or one of the desired cells first. Then, we will apply Format Painter (paint brush icon in Home tab) by double clicking on it, then apply to multiple cells.
Select a cell where you want to highlight (or a blank cell) and highlight in a color you like. Press Ctrl+1 to go to Format Cells dialogue.
Go to Number tab and choose Custom.
Select one of the number formats with colours for negative numbers. For example, #,##0;[Red]-#,##0. If you do this step correctly, you are half way there to using SUMIF() on colored cells.
One way to choose number formats if you have no idea is to firstly change the cell to a format you want in Home tab’s Number group. It is usually Comma, Accounting or Currency. Make sure that you have the decimal places and thousands separator adjusted, then go to Custom in Number tab as mentioned above. This way, Excel should select the one closest to your option. If there is no color tag for negative numbers, add it yourself.
You can use [black] if you don’t want negative numbers to appear differently.
Excel Custom Number Formats
Excel custom number format uses the following structure:
You can put [Color] in front of any section.
[Color] can be either [black], [white], [red], [green], [blue], [yellow], [magenta] or [cyan]
SUMIF Color to Sum Cells
Now that your cells are highlighted, and with a coloured number format for negative numbers, you can use SUMIF() function with the following arguments to sum cells by color:
Range: Helper column with CELL() functions
Criteria: 1. CELL() function returns 1 if the cell has a colour format for negative numbers. 0 otherwise.
SUM_Range: The range with colored cells that you want to sum. In this case, column B.
By using simple hacks what was impossible becomes possible in Excel.
If you followed the steps described above correctly, you should be able to “Excel Sum Colored Cells” with no problem.