In short, No In you have further interest in this query, please start your own thread as the forum rules require.

Please see Forum Rule 4 about hijacking and start a new thread for your query.

Sum By Font Color In Excel How To Start A

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread.

Search How Tos Excel Functions Excel VBA How to Find Circular References in Excel Flash Fill in Excel - Complete Guide How to Enable Macros in Excel Sum Cells based on Background Color How to Spell Check in Excel Counting Unique Values In Excel How to Insert a Checkbox in Excel Excel Function Keys and Shortcuts How to Insert Multiple Rows in Excel Use an Image as a Background in Excel How to Find External Refrences in Excel How to Concatenate in Excel Excel IF Statement - How to Use Excel FILTER Function - How to Use Excel VLOOKUP Function - How to Use Excel HLOOKUP Function - How to Use Excel ROUND Function - Complete Guide Excel SUMIF and SUMIFS - Complete Guide Excel COUNTIF Function - How to Use Excel OFFSET Function - How to Use Excel TEXT Function - How to Use Excel MATCH Function - How to Use Excel IFERROR Function - How to Use VBA Wait and Sleep Functions VBA On Error Statement Handling Errors VBA Substring How to Substring in Excel VBA VBA VLOOKUP With Examples VBA MsgBox - How to Use VBA For, For Each, Do While Do Until Loops VBA Split Function - How to Use VBA DIR Function - How to Use VBA IF Function - How to Use VBA InStr and InStrRev - How to Use VBA REPLACE Function - How to Use Exceltrick.com is your one-stop shop to learn all excel tricks, formulas, how-tos shortcuts.

His task was to add the contents of certain cells based on their background colors.

As we all know, Excel by default has no formula or feature to calculate such a thing.

So, in this post I will share few methods that will help you to achieve this.

To make the task more clear lets have a look at the below image.

This image depicts that here we dont need the total sum of all the elements but instead we want the sum of elements that have the same background color.

Recommended Reading: Weighted SUM in Excel Table of Contents Method 1: SUM cells on the basis of background colour using SUMIF Formula: So, whats the faster alternative Follow the below steps to use the UDF: Method 2: Using a much faster and better UDF: How to use this UDF: Method 1: SUM cells on the basis of background colour using SUMIF Formula: We know that SUMIF function is a combination of SUM and IF formula and hence SUMIF can come quite handy for adding cells based on color.

If you dont know how to use a SUMIF Function, then before going any further I would strongly suggest you to read this post.

First of all lets try to understand how we are going to do this: Consider we have a table as shown in the below image.

Next, we will add one more column to this table where we will manually type the background colors of their adjacent cells as shown in the below image.

But, as we can see that this method is quite cumbersome, particularly if we need to use this on lists with hundreds of elements.

So, whats the faster alternative To make the above process easier to use we need to reduce the effort of writing cell background colors manually.

So, for this task we can use a small user defined function (UDF) which will do the trick for us.

Function ColorIndex (CellColor As Range) ColorIndex CellColor.Interior.ColorIndex End Function Note: This function does not return the color name but it returns the color index which is also a unique value and can be used in our task.

First of all open your worksheet where you need to add the cells based on background colors.

Next, press ALT F11 to open the VB Editor.

Navigate to Insert Module.

After this, paste the ColorIndex UDF in the Editor.

Now, add one column next to the range that you wish to sum up.

In this new column enter the formula as: ColorIndex ( refrenceofcellwhosebackgroundcolourindexyouwishknow ) 5.

After that, drag this formula to the whole range.

Now, you can use the SUMIF function to add the cells that have same background color as shown in the above image.

Method 2: Using a much faster and better UDF: The UDF that we are going to use in this method is simply an extension of the above used function.

Open your target worksheet.

Press ALT F11 to open the VBA Editor and navigate to Insert Module.

Paste the SumByColor Function in the Editor.

Now, simply type the SumByColor function to call it and pass the following arguments: SumByColor ( cellwithbackgroundcolorthatyouwishsum, Rangetobesummedup ) Note: In the formula shown in above image instead of the first argument A2 we could have also used any one of A2, A5, A8, A10, A12.

In our case we can use the following formulas: Sum of Yellow Cells: SumByColor (A2,A2:A13) As A2 is the address of yellow cell and A2: A13 is the range to be added Sum of Orange Cells: SumByColor (A3,A2:A13) As A3 is the address of orange cell and A2: A13 is the range to be added Sum of Green Cells: SumByColor (A4,A2:A13) As A2 is the address of green cell and A2: A13 is the range to be added So, this was all from me about this topic.

Sum By Font Color In Excel Download The Sample

Dont forget to download the sample spreadsheet link and do let me know in case you face any issues while using these methods.

Recommended Reading Filed Under: How Tos Tagged With: Excel All Versions About Ankit Kaul Well, I am Ankit Kaul, the founder of Excel Trick.

Sum By Font Color In Excel For Mac