So let’s get this out the way straight away. There is currently no formula in Excel that allows you to count or sum coloured cells e.g. all the red cells. There is no count or sum IF the cell is red or something like that. You can’t even check if there is a colour in a cell with a formula. You can use VBA, but these articles are for counting or summing coloured cells in Excel without VBA.
We are going to look at
- Formulas that notice colour
- Tools that notice colour
- How to combine the tools and some functions to make it possible to sum or count cells with certain colours in them
- What should you do if you want to use colours but want them to be summable, countable or
Excel cell colour formula
The only function that notices that a cell has a colour in it is the CELL function. Even this is not perfect as it only notices if the FORMAT has been set to change colour for a negative number.
So if you type =CELL(“color”, the cell you want to look at), it will return a 1 or a 0.
1 means that the formatting is such that it will change colour with a negative number and a result of 0 means it won’t.
Notice in the picture below both D2 and D3 have a 1 which means that if they are negative numbers, they will turn red, and in D4 it is 0. As you can see in C4, we have a negative number which is in normal colour.
This will also work if you use the Custom Format option to change the colour used for a negative (e.g. make the cell blue if negative).
Tools in Excel that notice colours
Although there are no formula functions that notice colours, there are 3 tools that do notice colours being:
- The Data Filter,
- The Find tool, and
- Data Sorting tool.
In other articles we will show how you can use these tools to count, sum, average etc cells based on their colours in the quickest possible way without VBA.
The Data Filter and colours
The Data Filter can filter cells based on colours. As shown below, when the data filter is on, you can click on the relevant columns dropdown and choose to filter by colours.
The end result is that it only shows you cells with that colour e.g.
With this small example you could now manually add/ count/ average based on this but there are ways to do this quicker and more efficiently with the Data Filter. Read about the other ways to use the Data Filter to sum coloured cells
The Find/ Replace tool and colours
The FIND tool can also be used to find cells with certain colours. A good way is to highlight the relevant column, click on HOME then FIND & SELECT (far right) then FIND.
As shown below you don’t need to put anything in the Find What, but you must first click the OPTIONS button to see the FORMAT button.
Then click on the Format dropdown and choose Format. You will see the normal Format Cells options and in this case we need to choose the FILL tab and choose the red colour (but note that you can search for almost any format within your spreadsheet)
When we click OK we can choose to FIND NEXT (one at a time) or FIND ALL (all cells with red as a fill format).
Below we have shown the result of the FIND ALL option. You will note that it lists all the cells that have a red fill, and at the very bottom it tells you how many cells were found (so it counted the number of cells with red in them which is a start).
Couple of notes about using FIND like this:
- You should highlight the column you are interested in, otherwise Excel will look in all the cells in that sheet (unless that is what you want).
- You should have the Within option set as Sheets otherwise it looks through your whole workbook.
- Although one of the options under FORMAT is ‘Choose Format from cell’ you have to be careful, because it then looks for cells that have exactly the same format. So if the one cell you used as an example had a border on the top and was red, then it will only find all cells that have a top border and red colour and the exact number format and any other formats that may be on that cell.
This is relatively useful, especially if you want to COUNT the cells with colours but there are other ways to sum all the cells with colours using this tool.
Using Data Sort to find the cells with colours
You can also sort your cells based on their colours. This may help with the summing or counting process as it will be easier to do it manually but it will change the order of your cells which may be important to you.
The process is to:
- Highlight the cells with the information,
- Under the DATA tab click on SORT,
- Choose the column you want to sort on, change the Sort On drop down to Cell Color (note that there are some other useful options e.g. cell icons), and choose the colour.
- Now click the Add Level button and specify what the next colour should be.
- Continue until you have all the colours specified.
The end result will be the data sorted in colour order. It should now be easier to sum, count or average but as mentioned previously you may not want to mess with the order of your data.
Conclusion about working with colours in cells
The methods shown above are better then manually trying to work this out but with a few simple tricks you can make it easier (until Excel comes up with a function that does this).