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

 

2,445 thoughts on “How to Sum a Range of Cells by Background Colour

  1. healthy fresh recipes

    An impressive share! I have just forwarded this onto a friend who has been conducting a little homework on this. And he in fact bought me breakfast because I discovered it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending the time to talk about this matter here on your blog.

  2. Assistir Jogo Grátis No Celular

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  3. 카지노 사이트

    Howdy! Do you know if they make any plugins to assist with
    SEO? I’m trying to get my blog to rank for some targeted keywords but
    I’m not seeing very good gains. If you know of any please share.
    Appreciate it!

  4. Agen Poker Terpercaya

    Please let me know if you’re looking for a article writer for your blog. You have some really good posts and I believe I would be a good asset. If you ever want to take some of the load off, I’d love to write some material for your blog in exchange for a link back to mine. Please send me an e-mail if interested. Regards!|

  5. http://143.95.144.116/

    Hi there! This post couldn’t be written any better! Looking through this article reminds me of my previous roommate! He constantly kept preaching about this. I most certainly will forward this article to him. Fairly certain he’s going to have a very good read. Thank you for sharing!

  6. reindeermovie.video.blog

    Oh my goodness! Incredible article dude! Thanks, However I am experiencing difficulties with your
    RSS. I don’t know the reason why I can’t subscribe to it.
    Is there anyone else getting similar RSS issues? Anybody who knows the answer will
    you kindly respond? Thanks!!

  7. 인터넷 카지노

    I do not even know how I ended up here, but I thought this post was good.

    I do not know who you are but certainly you are going to a famous blogger if
    you are not already 😉 Cheers!

  8. atm 카지노

    Hello there, I discovered your web site by means of Google while looking for a similar matter,
    your web site got here up, it appears great.
    I’ve bookmarked it in my google bookmarks.
    Hi there, simply became alert to your weblog through Google, and located
    that it is truly informative. I’m going to watch out for brussels.
    I’ll appreciate if you proceed this in future. Lots of folks will probably be benefited from your writing.
    Cheers!

  9. pothosvine.wordpress.com

    I was very happy to discover this web site. I want to to thank you
    for ones time for this fantastic read!! I definitely enjoyed every little bit of it and I have you book marked
    to see new stuff on your site.

  10. 예스 카지노

    I will right away take hold of your rss as I can’t in finding your e-mail
    subscription hyperlink or e-newsletter service. Do you’ve any?
    Please permit me recognise so that I could subscribe.
    Thanks.

  11. 바카라 사이트

    Heya i’m for the primary time here. I found this board
    and I find It truly useful & it helped me out much.
    I am hoping to provide something again and
    aid others like you helped me.

  12. naija music mp3

    Hey there just wanted to give you a quick heads up.
    The text in your post seem to be running off the screen in Internet explorer.

    I’m not sure if this is a formatting issue or something to do with web browser compatibility but I thought
    I’d post to let you know. The style and design look great though!
    Hope you get the issue fixed soon. Thanks

  13. calem987.art.blog

    Hello! Would you mind if I share your blog with my zynga
    group? There’s a lot of folks that I think would really
    appreciate your content. Please let me know.
    Cheers

  14. 슈퍼 카지노

    Hello my loved one! I wish to say that this post is amazing,
    nice written and include approximately all important
    infos. I would like to peer extra posts like this .

  15. 바카라 게임

    Yesterday, while I was at work, my sister stole my apple ipad and tested
    to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views.
    I know this is entirely off topic but I had to share it with someone!

  16. 바카라 게임

    I simply could not leave your website prior to suggesting that I
    really loved the standard information a person supply on your
    guests? Is going to be again often in order to investigate cross-check new posts

  17. 더킹 카지노

    Thanks for any other informative website. Where else may just I am getting that
    kind of info written in such an ideal method?
    I have a challenge that I’m simply now operating on,
    and I have been at the glance out for such information.

  18. guanaceo.design.blog

    I really like your blog.. very nice colors & theme.
    Did you design this website yourself or did you hire someone to do it for you?
    Plz respond as I’m looking to create my own blog and
    would like to find out where u got this from.
    thanks a lot

  19. 예스 카지노

    My developer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using Movable-type on a variety of websites for about a year and am
    anxious about switching to another platform.

    I have heard very good things about blogengine.net. Is
    there a way I can import all my wordpress content into it?
    Any kind of help would be greatly appreciated!

  20. poboleda.org

    Great web site you’ve got here.. It’s difficult to find high-quality writing like yours these days. I seriously appreciate people like you! Take care!!

  21. 바카라 사이트

    I like the valuable information you provide in your
    articles. I will bookmark your weblog and check again here regularly.
    I am quite sure I will learn lots of new stuff right here!
    Best of luck for the next!

  22. SM 카지노

    Hello, Neat post. There’s an issue with your site in internet explorer,
    may check this? IE still is the market chief and a good section of other folks will miss your wonderful writing due to this problem.

  23. 예스 카지노

    May I simply just say what a comfort to uncover someone who actually
    understands what they are discussing on the net.
    You definitely understand how to bring an issue to light and make it
    important. More and more people need to read this and understand this side
    of your story. I was surprised you’re not more popular since you definitely have the gift.

  24. XO 카지노

    Howdy! This is my first visit to your blog! We are
    a group of volunteers and starting a new project in a community in the same niche.

    Your blog provided us useful information to work on.
    You have done a marvellous job!

  25. 바카라 사이트

    Greate pieces. Keep posting such kind of information on your blog.
    Im really impressed by it.
    Hey there, You’ve performed an excellent job. I will definitely digg it and personally recommend
    to my friends. I am sure they’ll be benefited from this site.

  26. 더킹 카지노

    Great beat ! I wish to apprentice while you amend your site, how could i subscribe for a blog website?

    The account helped me a applicable deal.
    I have been a little bit familiar of this your broadcast provided brilliant transparent concept

  27. 파라오 카지노

    You actually make it seem so easy with your presentation but I find this matter to
    be actually something that I think I would never understand.
    It seems too complex and extremely broad for me. I’m looking forward for your next post, I’ll try to get the hang of it!

  28. 谷歌SEO

    I don’t even know how I ended up here, but I thought this post
    was great. I don’t know who you are but certainly you are going to a famous blogger if you are not already 😉 Cheers!

  29. vivoslot.net

    Greetings! Very helpful advice within this post! It is the little changes which will make the most significant changes. Many thanks for sharing!

  30. prairiegentian.wordpress.com

    I was curious if you ever considered changing the structure of your site?

    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect with it better.

    Youve got an awful lot of text for only having 1 or 2 images.
    Maybe you could space it out better?

  31. 우리 카지노

    First of all I would like to say excellent blog!

    I had a quick question which I’d like to ask if you don’t mind.

    I was curious to find out how you center yourself and clear your head prior to writing.
    I’ve had difficulty clearing my thoughts in getting my ideas out there.
    I truly do take pleasure in writing however it just seems
    like the first 10 to 15 minutes tend to be wasted just trying to figure out how to begin.
    Any recommendations or hints? Cheers!

  32. acompanhantes de campinas

    I think this is among the most significant information for me.
    And i am glad reading your article. But should
    remark on some general things, The site style is perfect, the articles is really nice :
    D. Good job, cheers

Comments are closed.