How to Sum a Range of Cells by Background Colour

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!?

 

9 thoughts on “How to Sum a Range of Cells by Background Colour

  1. Steve F

    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.

  2. Excel Geek

    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!! 😉

  3. souk3650

    Has anyone discovered how to “sumcolourif”??

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

    thx

    J

  4. Kristie

    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! 🙂

  5. Excel Geek

    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

  6. Excel Geek

    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!

  7. Sarah

    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

  8. Ellie

    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?

  9. الاذاعة المصرية

    Hello,I check your new stuff named “Help with Microsoft Office Excel | How to Sum a Range of Cells by Background Colour | Formula, Functions, Macros, Spreadsheets, & VBA” like every week.Your story-telling style is awesome, keep it up! And you can look our website about الاذاعة المصرية.

Leave a Reply

Your email address will not be published. Required fields are marked *