Marking cells with a color using a fill or font color is very convenient and visual. Unless you are color blind, of course 🙂 Difficulties arise when it becomes necessary to make a report on such a colored table. And if Excel has learned to filter and sort by color in the latest versions, it still does not know how to summarize by color.
To fix this significant drawback, you can use simple user-defined macro functions in Visual Basic, which will allow us to sum / count the number / arithmetic mean of cells with a certain fill / font color.
On the Advanced tab developer (Developer) click the Visual Basic button or keyboard shortcut Alt+F11to open the macro editor. If you do not see such a tab, then enable it in the settings File – Options – Ribbon Setup (File — Options — Customize Ribbon).
In the editor window, insert a new module through the menu Insert – Module and copy the text of the following functions there:
Function CountByColor(DataRange As Range, ColorSample As Range) As Long Dim cell As Range, n As Long For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then n = n + 1 Next cell CountByColor = n End Function Function SumByColor(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value Next cell SumByColor = total End Function Function AverageByColor(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double, n As Long For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value n = n + 1 End If Next cell AverageByColor = total / n End Function
As you can easily figure out, the first function here calculates the number of cells with a given fill color, the second – the sum, and the third – the arithmetic mean. All functions have two arguments:
- DataRange – a range of source cells with numbers colored in different colors
- ColorSample – cell whose fill color we take as a sample
If we now return to Excel, then in the Function Wizard (tab formula – button Insert function) in the category that appeared there User Defined (User Defined) you can find our functions and paste them on the sheet. Or directly enter them into the formula bar, like any other Excel functions:
Adding conditions
A similar approach can be easily scaled by adding, if necessary, additional conditions to the check (the if … then … command). So, for example, if we need to take into account not only the fill color, but also the font color when calculating the arithmetic mean (i.e., count not just yellow, but yellow-red cells), then our macro function code will look like this:
Function AverageByColor2(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double, n As Long For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then total = total + cell.Value n = n + 1 End If Next cell AverageByColor2 = total / n End Function
The difference is only in the “AND” added through the logical connective (and) the condition for checking whether the font color of the next checked cell matches cell.Font.Color sample cell font color ColorSample.Font.Color.
Restrictions and nuances of recalculation
The macro functions we created have 2 important nuances.
Firstly, these functions “do not see” the fill created using conditional formatting, i.e. work only with the color that was set manually for the cells.
Secondly, unfortunately, changing the fill color or font color of a cell is not considered by Excel as a change in its contents, therefore it does not trigger formula recalculation. That is, when repainting the original cells with numbers in other colors, the total amount/average/number by our functions will not be automatically recalculated.
It is impossible to completely solve this problem, but there are several ways to get around it:
- Double-click on the cells with our formula with the left mouse button and click on Enter, i.e. simulate re-entering a function into a cell – Excel will recalculate it and give an updated result.
- You can use the keyboard shortcut Ctrl+Alt+F9, which will force Excel to recalculate all formulas and functions, regardless of whether their source data has changed. But this combination will need to be remembered to be pressed every time the initial data changes.
- Add to the code of our macro functions (anywhere) the command Application.Volatile True. This Visual Basic language command causes Excel to recalculate the results of our function when any cell on the worksheet is changed (or when F9). However, in this case, the speed of our functions will noticeably decrease, because. their recalculation will occur constantly – even when we did not change the colors or cell values in the original data. Use this method carefully.
And remember that our function iterates over all (and empty too) cells in the range DataRange and do not set a whole column as the first argument – it will take a long time to “think” 🙂
- Sort rows by color
- Functions for counting the number and sum of cells by color from the PLEX add-on
- What is “conditional formatting hell” and how to beat it