Excel users typically like to colour cells to highlight certain items. The problem is once you have all the cells highlighted, you will inevitably need to count or add them. There is currently no SUMIF, COUNTIF, AVERAGEIF that works on colours. So you need to trick Excel. One of the ways is to use your FIND tool and the Status bar.
In the example below we want to SUM, COUNT and AVERAGE all the red cells in columns C to E.
Count colored cells with FIND/ REPLACE
The steps to follow would be:
- highlight the area within which Excel must search (C2 to E27) otherwise it will look everywhere for the red cells
- Activate the FIND tool (CTRL + F)
- Click on the FORMAT button on the right
- In the FIND FORMAT box, choose the FILL tab and click on the red option and click OK.
- Click on the FIND ALL button
- You will see something like this. You need to activate all the cells Excel has found. You do this by clicking on the first cell found, holding SHIFT down, and clicking on the last cell in the list.
- You will notice that all the red cells are now highlighted and if you look in your status bar at the bottom, it tells you the average, count, sum and more of the active cells.
Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.