sum cell colour

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » SUM by Cell Colour

# SUM by Cell Colour

Resolved · High Priority · Version 365

### SUM by Cell Colour

I have a list of numbers (in £) which I turn green when the amount has been received. Is there a way to create a sum to automatically just include those which are in a green cell, from a list?

### RE: 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

### RE: SUM by Cell Colour

Hi,

Thanks for your help and clear guidance.

Unfortunately for the table I have created I need all cells to be visible at once (green and no fill), so filtering by colour doesn't help.

I may have not made this clear in my original query, but all of the cells have figures in. Although the sum should be looking at all the cells, it should only sum together those highlighted in green.

If the method I have currently won't work, is there a different solution you may be able to offer?

### RE: SUM by Cell Colour

Hi Millie,

You will not find an easy way of doing what want.

The first example in the video is very similar to what I suggested in my first reply.

The second example is to write a VBA function. You can find the code below.

Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
Dim rcell As Range
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

You will not have other options to do what you want.

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

### RE: SUM by Cell Colour

The video was really helpful and did exactly what I needed. Thanks

### RE: SUM by Cell Colour

Hello Millie,

Jens gave you the perfect solution to your question.

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

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

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Pivot table grouping

If you want to group items in a pivot table together, simply highlight the labels for the fields either with the shift key (if adjacent)or with the contral key if they are not next to each other.
Right click and choose group. Give the cell a name.

When you double click on this cell it will either expamd or collapse your grouped area

View all Excel hints and tips