Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Sum in excel based on cell colour

Posted on 2014-07-10
Medium Priority
Last Modified: 2014-07-11
hi Folks
Is there a way to sum a range based on its colour e.g. in the attached sheet. All the yellow cells added up together are 500. Is there some way to use the Sumif or some other way to automatically add up cells based on their colour. Thanks.
Question by:agwalsh
  • 2
LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 40189920

pls try this UDF

Function SumByColor(rng As Range)
ColIdx = Application.Caller.Interior.ColorIndex
For Each c In rng
  If c.Interior.ColorIndex = ColIdx Then
    mySum = mySum + c
  End If
Next c
SumByColor = mySum
End Function


Author Comment

ID: 40189965
Brilliant...does the job. I just got thrown by the fact that I have to put the UDF in the actual coloured box :-) to get it to work properly...thank you :-)

Author Closing Comment

ID: 40189966
Does exactly what it says on the tin. If using this, make sure you put the function in the cell with the colour you want to sum by.

Expert Comment

by:Naresh Patel
ID: 40190003

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question