No macros, no formulas, no pain :) The All Colors option works basically in the same way, except that you do not have to choose the color.In the "Show results for" section, you can select any of the following options: Count, Sum, Average, Maximum or Minimum value.
But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.
Apart from using third-party add-ins, there is only one possible solution - utilize User Defined Functions.
All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here! Color For ind Cur Cell = 1 To (cnt Cells - 1) If ind Ref Color = Selection(ind Cur Cell). Sum(Selection(ind Cur Cell), sum Res) End If Next Msg Box "Count=" & cnt Res & vb Cr Lf & "Sum= " & sum Res & vb Cr Lf & vb Cr Lf & _ "Color=" & Left("000000", 6 - Len(Hex(ind Ref Color))) & _ Hex(ind Ref Color) & vb Cr Lf, , "Count & Sum by Conditional Format color" End Sub functions ready for use and try them on your data. When we published this article, we hoped it would be popular because we used to get a lot of questions about how to count and sum cells by color in Excel.
The VBA code below overcomes the above mentioned limitations and works in Microsoft Excel 2010, Excel 2013 and Excel 2016 spreadsheets with all types of condition formatting (kudos to Alex again! As a result, it displays the number of colored cells and the sum of values in those cells, no matter which type of conditional formats are used in a sheet. But even in our wildest expectations, we did not think it was going to be that popular!
Of course if you have that little VBA gem that makes the magic happen : ) But what if you do not color cells manually and rather use conditional formatting, as we discussed in these two articles How to change the background color of cells and How to change a row's color based on cell value?