# Tom’s Tutorials for Excel: Sum and Count Cells By Color

Tom’s Tutorials for Excel: Sum and Count Cells By Color

To sum, count, or perform a mathematical operation on a range of colored cells, you will need VBA. Excel does not have a built-in native worksheet function to mathematically evaluate contents of colored cells, such as summing the numbers in all red-colored cells. Note, this topic deals with cells whose actual pattern color was changed with the color palette, as opposed to color by Conditional Formatting which only changes the appearance of a cell’s color but really does not change the cell’s Interior ColorIndex property. If you want to count or sum cells that are colored by Conditional Formatting, a native worksheet function would do that by evaluating the formatted condition.

In the below picture for example, range A1:B10 has cells that are colored red, blue, yellow, and gray, and some cells have no color. The picture shows where three User Defined Functions (UDFs) have been entered into cells D2, F2, and H2 to demonstrate how you can sum or count colored cells.

The UDF in cell D2 sees the color of the cell it is in, and sums cells having that color.
The UDF in cell F2 evaluates for a color index number, for example #6 which is yellow.
The UDF in cell H2 is an example of how to return the count of all cells having any color.

From this point you can modify the UDFs’ code to average or subtotal for certain color(s), or to count only specific colors, or perform whatever math operation you wish.

Here are the three User Defined Functions that are being utilized by cells D2, F2, and H2 respectively. Comment notes are in green to help explain each code line.

`Function SumCurrentColor(RangeToSum As Range) As Long`
`'Update the function when a cell is changed on the sheet.`
`Application.Volatile`
`'Declare the necessary variables.`
`Dim ColorID As Integer, ColorCell As Range, mySum As Double`
`'Identify the ColorID variable so you know what color to look for.`
`ColorID = Range(Application.Caller.Address).Interior.ColorIndex`
`'Loop through each cell in the range.`
`For Each ColorCell In RangeToSum`
```'If the cell’s color matches the color we are looking for, 'keep a running subtotal by adding the cell’s number value 'to the mySum variable.```
```If ColorCell.Interior.ColorIndex = ColorID Then mySum = mySum + ColorCell.Value Next ColorCell```
```'The cells have all been evaluated, so you can define the 'SumCurrentColor function by setting it equal to the mySum variable.```
```SumCurrentColor = mySum End Function```

`Function SumSingleColor(RangeToSum As Range, iColor As Integer) As Long`
`'Update the function when a cell is changed on the sheet.`
`Application.Volatile`
`'Declare the necessary variables.`
`Dim ColorCell As Range, mySum As Double`
`'Loop through each cell in the range.`
`For Each ColorCell In RangeToSum`
```'If the cell’s color matches the iColor color index variable, keep a 'running subtotal by adding the cell’s number value to the mySum variable.```
```If ColorCell.Interior.ColorIndex = iColor Then mySum = mySum + ColorCell.Value Next ColorCell```
```'The cells have all been evaluated, so you can define the 'SumSingleColor function by setting it equal to the mySum variable.```
```SumSingleColor = mySum End Function Function CountColors(RangeToCount As Range) As Long 'Declare the necessary variables. Dim ColorCell As Range, myCount As Double 'Loop through each cell in the range. For Each ColorCell In RangeToCount 'If the cell has any color, add it to the running count. If ColorCell.Interior.ColorIndex > 0 Then myCount = myCount + 1 Next ColorCell 'The cells have all been evaluated, so you can define the 'CountColors function by setting it equal to the myCount variable. CountColors = myCount End Function```

``` var hupso_services_t=new Array("Twitter","Facebook","Google Plus","Pinterest","Linkedin","Reddit");var hupso_background_t="#EAF4FF";var hupso_border_t="#66CCFF";var hupso_toolbar_size_t="medium";var hupso_image_folder_url = "";var hupso_url_t="";var hupso_title_t="Tom%27s%20Tutorials%20for%20Excel%3A%20Sum%20and%20Count%20Cells%20By%20Color"; ```
``` ‹ Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string Tom’s Tutorials For Excel: Export Excel Spreadsheet Into Access Database Table › Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, User-Defined Functions ```
``` 4 comments on “Tom’s Tutorials for Excel: Sum and Count Cells By Color” Taylor says: July 8, 2014 at 9:11 am Tom, Great tutorial on summing or counting based on cell color. How would I subtotal based on cell color? Thank you. Reply Tom Urtis says: July 9, 2014 at 10:43 am Thanks for following my Excel blog page. Can you give me some context on what you want to subtotal by? For example, in the picture, the red or the gray or the blue or the yellow cells would be subtotaled by definition of their cell color. In other words, using column A in my picture, the total of all numbers is 43. If only the red cells were totaled, that would essentially be a subtotal (9) of red cells in column A. I get the feeling you are looking for something more complex than that but I can’t tell what that would be from your question. Post back with more details and I’ll try to help as best I can for a solution you can use. Reply Rajesh Sinha says: November 1, 2017 at 9:49 pm Tom it’s quite useful,, handy too. Is it possible to sum by colour using Excel Function? Reply Tom Urtis says: November 1, 2017 at 10:04 pm There’s no native function that can do this, for a cell’s interior color. Conditional Formatting is different, where you can sum and average based on the conditions changing the color. Reply Leave a Reply Your email address will not be published. Required fields are marked *Comment * Name * E-mail * Δdocument.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); ```
``` CATEGORIESActiveX / Form Controls (10)Array Formulas (27)Cells Ranges Lists (122)Charts and Chart Sheets (1)Comments / Screen Tips (9)Conditional Formatting (15)Cut Copy Paste Clear Fill (16)Data validation (12)Date Time (53)Duplicates Uniques (13)Filter Subtotal Group (9)Find Replace Substitute (18)Formats Fonts Styles Colors (49)Formulas Functions (152)Keyboard Mouse (35)Links Hyperlinks Web Email (12)Macros Events Classes API (110)Numbers and Decimals (49)Pictures Shapes Graphics (4)Pivot Tables / Pivot Charts (10)Printing Page Setup (7)Sort (7)Text and Constants (19)Tips and Tricks (55)User-Defined Functions (6)UserForms (10)Word Outlook Access PPT (8)Worksheets Workbooks (60) Tweets by @TomUrtis !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); ```
``` ```
``` (function () { var c = document.body.className; c = c.replace(/woocommerce-no-js/, 'woocommerce-js'); document.body.className = c; })(); /* <![CDATA[ */ var BG_SHCE_USE_EFFECTS = "0"; var BG_SHCE_TOGGLE_SPEED = "400"; var BG_SHCE_TOGGLE_OPTIONS = "none"; var BG_SHCE_TOGGLE_EFFECT = "blind"; /* ]]> */ /* <![CDATA[ */ var wc_order_attribution = {"params":{"lifetime":1.0000000000000000818030539140313095458623138256371021270751953125e-5,"session":30,"ajaxurl":"https:\/\/www.atlaspm.com\/wp-admin\/admin-ajax.php","prefix":"wc_order_attribution_","allowTracking":true},"fields":{"source_type":"current.typ","referrer":"current_add.rf","utm_campaign":"current.cmp","utm_source":"current.src","utm_medium":"current.mdm","utm_content":"current.cnt","utm_id":"current.id","utm_term":"current.trm","session_entry":"current_add.ep","session_start_time":"current_add.fd","session_pages":"session.pgs","session_count":"udata.vst","user_agent":"udata.uag"}}; /* ]]> */ San Francisco, California1-925-310-4170tom@atlaspm.comdocument.body.style.setProperty( "padding-bottom", document.getElementById('scb-wrapper').offsetHeight+'px', "important" ); ```