SUM by Cell Colour

Hi Millie,

Thank you for the forum question.

You can sum by colour by using the Table tool you saw on the Excel Intermediate course, but you will need to add a total row to the table. This option you can find after you have converted the range to a table under the Table Design tab in the Table Style options.

By filter the column in which you have the coloured cells by colour, the total row will summarise the colour you filter on.

Otherwise you can only do it by writing VBA code.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hello Millie,

Jens gave you the perfect solution to your question.

Here are the steps to make it easier for you to achieve this:

1. Click in any cell in your data (make sure your table has headers)

2. In the Home ribbon, click 'Format as Table'

3. Select a theme (I would go for a mainly white theme)

4. In the top ribbon, click the 'Table Design' tab, then tick the 'Total Row' box - a total cell will appear below the last row of data

5. In the first cell of the column with the green cells, click the drop-down filter button

6. Select 'Filter by Color' > 'Filter by Cell Color'

This should leave only the green cells visible and give you the total for your green cells.

I hope this helps.

Kind regards
Marius Barnard
STL