# Just recently I was asked if it was possible to sum a range of cells by their cell colour, rather than counting the number of cells with a matching colour. Well it is. To sum a range of cells based upon their background colour requires a custom function to be written and the a small formula to be written on the worksheet.

I was asked was it possible to sum a range of cells based on two different cell colours. Red and Yellow. Red representing a negative figure and yellow a positive number. To start with you need to add a custom function to a new module in Excel’s Visual Basic editor. Once you have created a new module copy the below function and paste it in to the module.

`Function SumIfByColour(InputRange As Range, ColorRange As Range) As Double`

`Dim clr As Range`

`Dim ColourSum As Long`

`Dim ColorIndex As Integer`

`PositiveColour = 6 'Yellow Background`

`NegativeColour = 3 'Red Background`

`ColourSum = 0`

`On Error Resume Next ' ignore cells without values`

`For Each clr In InputRange.Cells`

`If clr.Interior.ColorIndex = PositiveColour Or clr.Interior.ColorIndex = NegativeColour Then`

`ColourSum = ColourSum + clr.Value`

`End If`

`Next clr`

`On Error GoTo 0`

`Set clr = Nothing`

`SumIfByColour = ColourSum`

`End Function`

Once you have done this go back to your worksheet. Now let’s assume you have a list of numbers, positive and negative in column A ranging from A1 to A20. At the bottom of your list, or in cell B1 type the following formula:

`=SumIfByColour(A1:A20,A1:A20)`

Once typed hit enter and you will have your total! 🙂

You may be asking, what if I have different colour backgrounds, or only one colour?

To sum different colours than those in this example you simply need to get the ColorIndex (which is a number) for the colours you are using. Getting the colour index is fairly easy and there is a guide here. Or you could simply look up the ColorIndex value using this chart I have prepared earlier!

I only have one colour I hear you say…. Well just modify the code slightly as shown below.

`NegativeColour = 3 'Red Background`

– REMOVE THIS LINE

CHANGE THIS LINE

`If clr.Interior.ColorIndex = PositiveColour Or clr.Interior.ColorIndex = NegativeColour Then`

TO

`If clr.Interior.ColorIndex = PositiveColour Then`

Hey presto!

I hope you found this post useful!?

Great work, very nice.

To save looking up colour charts, I’d possibly have added at another parameter with a link to a cell that contains the colour combination you’d like to sum.

e.g

=SumIfByColour(A1:A20,A1:A20,A1)

then PositiveColour = thirdParameterName.Interior.ColorIndex

Keep up the good work man.

Hey

Thanks for the comment! I agree with your thought and I had considered using a third cell to be the parameter for the colour. But I decided on this option so that you don’t have random coloured cells on the sheet. But then of course as you point out you need to know the colour index! So many ways of doing things!! 😉

Has anyone discovered how to “sumcolourif”??

I’m looking to sum colored cells based on a date in an adjacent cell…

thx

J

Have a question – using the =cellcolour(8,4) formula to find the background color of a cell – if I try to copy that formula down to find different colors in a range – it won’t work. The ‘8’ and the ‘4’ stay the same throughout the copy-down. I tried doing 4 in a row, manually, to create a pattern (such as copying down chronologically-numbered) – but that didn’t work either.

Any advice on how to get this to work?

Also – I use conditional formatting a LOT in my work (Analyst). I’d like to use your ‘IF’ formulae that incorporates a cell background color in my analytics – however, it doesn’t recognize a conditionally-formatted cell background color as being truly colored. Therefore, using your formula won’t work on conditionally-formatted cell background colors. (Make sense?). Any advice on this would be sooo appreciated toO! 🙂

Excellent work & great directions – THANK you for all you’ve published so far! 🙂

Hi Kristie

Try this to workout the cell colours.

Change view of the worksheet so that is in R1C1 mode. Rather than having letters at the top you will now have numbers. And then enter this formula into the cell to give you the colour index numbers…

`=CellColour(ROW(RC[-1]),1)`

This formula presumes you are entering the formula into cell B1 and the colours start in A1.

Thanks

Apologies, yes I have seen there is a problem with counting cell colours if they have been coloured using conditional formating. I am still trying to get a formula to work with this. My day job is as an analyst so I appreciate the problem it causes. I have used a workaround with numbers in a hidden cell. I’m hoping to get something working though on the formula side!

I am using the below function to sum staff holidays but am having trouble getting it to add up the totals correctly. for example on one row it rounds up totalling 7.5+7.5 as 16 but on another row it rounds down totalling 4.5 as 4. I have all cells formatted as numbers and 2 decimal places.

Any insight would be helpful.

Function SUMHOLIDAY(Inputrange As Range, colorrange As Range) As Double

Dim clr As Range

Dim coloursum As Long

Dim colorindex As Integer

HolidayColour = 44 ‘Orange background

coloursum = 0

On Error Resume Next ‘ ignore cells without values

For Each clr In Inputrange.Cells

If clr.Interior.colorindex = HolidayColour Then

coloursum = coloursum + clr.Value

End If

Next clr

On Error GoTo 0

Set clr = Nothing

SUMHOLIDAY = coloursum

End Function

I have followed this to the letter and am getting the following error:

COmpile error: Invalid outside Procedure

to which is highlights the colour code number in the Module….What am I doing wrong?