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

x
Solved

# Sum in excel based on cell colour

Posted on 2014-07-10
Medium Priority
319 Views
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.
sum-based-on-colour.xlsx
0
Question by:agwalsh
• 2

LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 40189920
Hi,

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

Regards
sum-based-on-colourV1.xlsm
0

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 :-)
0

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.
0

LVL 8

Expert Comment

ID: 40190003
0

## Featured Post

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…
###### Suggested Courses
Course of the Month13 days, 5 hours left to enroll

#### 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.