As indicated in a previous post, one of the tools that ‘notices’ colours in Excel cells is the Data Filter. By combining this with some other tools/ functions you can get a sum, count or average of the coloured cells.
YouTube Sums cells based on their background colour
Using the Status Bar and Data Filter to Count cells with colours
As shown previously, you can use the Filter to show only a certain colour. As shown below we have used a Filter in column C to show all the red cells. We have highlighted this column. Note that the formula in C85 (a sum of the column) gives 38 741 but notice that in the status bar it shows a sum of 5 982.
The Status bar is clever enough to only sum the cells it can see. So while a normal SUM formula in cell C85 would add everything you see including the hidden cells, the Status Bar only works with the cells that are visible.
As a result, by working with the Data Filter and Status Bar you can trick Excel into
- Summing by colours
- Getting the maximum or minimum of certain coloured cells
- Counting the cells with the colour that concerns you, and
- Averaging the cells with a certain colour
This is useful and better than doing it manually but you can’t copy and paste the results out. You will need to write them down somewhere or retype them.
Use the AGGREGATE function to sum cells by colour
Although there is no function that can sum cells by colour, there are some functions that can sum only the visible cells. By combining these functions with the Data Filter you can quickly work on the coloured cells.
So in the example below, in C83 we have a standard SUM function and it adds up everything you see and all the hidden cells. The AGGREGATE function allows you to specify that it must ignore the hidden cells (and other types of cells if required).
So in C84 we have started the AGGREGATE function and the first requirement is the function number. As we are trying to sum cells by colour we would choose the ‘9- SUM option’ but note all the available options including average, count, max and min.
The next thing AGGREGATE needs is an option. Do you want to ignore error values, hidden rows etc. In this case we want it to ignore the hidden rows so the correct option is ‘5- Ignore Hidden Rows’.
To finish off the AGGREGATE function you need to tell it where to look for the cells.
When you click enter, Excel will only sum the visible cells which, as a result of filtering on red only, is the same as summing the red cells only. In rows 87 to 93 you will see we have switched on some of the other functions within AGGREGATE. So a 1 is average the red cells, 2 and 3 are count the red cells etc.
Now you can copy and paste these numbers (as values) to use elsewhere.
Again this is not ideal. It would be better if there was a SUM IF the cell is red, but this is as good as it gets without using complex VBA.
Related
Summing, Counting, or Averaging visible cells only in Excel
Errors that can happen in simple SUM formulas
Add tick/ cross into your Data Validation and use it in IFs and Conditional Formatting