How to quickly count and sum colored cells in Excel

Sum and Count by Color add-in for Microsoft Excel 2016 – 2007

Count And Sum by Color is a helpful Excel add-in that lets you instantly calculate cells of the same tint. On this page you will find out how to use the tools All Colors and One color to perform summing and counting on the colored data in your spreadsheet.

How to work with Count And Sum by Color: 2 min video

How to count colored cells in Excel

If you work with tables that have color-coded cells, see the details below for a quick way to count them.

First open Excel worksheet where you need to count cells by color, click the Sum by Color icon in the Calculate group on the Ablebits Tools tab and pick one of two tools in the drop-down list:

Sum & Count by One Color

Use this tool to calculate cell data based on the selected color.

Click on the One Color icon under the Ablebits Data tab

It lets you select any shade within your table and see the Sum, Count, Average, Min and Max values displayed for this color in the add-in pane.

  1. The add-in picks the entire table by default. Use the field at the top to edit the range, or select the cells where you would like to count a color in your Excel worksheet.
  2. Click the Color Picker icon next to the Pick any cell with color field and select a pattern cell in your table that has the formatting you need.
  3. Specify if you are looking for the cells of certain Background or Font color by selecting the corresponding radio button.
  4. Choose to recalculate when selection changes if you prefer and click the Calculate button to see the results in the add-in pane. Press the Calculate button to see the results on the One Color pane
  5. Now you can paste the numbers you see into your Excel worksheet. To do this, click the Paste results… button in the bottom right corner of the add-in pane. You will see a dialog box where you can specify the top left cell for the count color results.

Sum & Count by All Colors

Use this tool o quickly see an overview of color-coded data in your table.  For example, if you use different hues to highlight prices from the vendors, it will quickly sum cells by color and show you the total for any group of numbers. To count colored cells in Excel, open the worksheet with your data and select the tool in the Sum by Color add-in list.

Run the add-in by clicking on its icon in Excel
  1. Choose or enter the data range in your sheet and you’ll see its address in the field at the top of the pane. Please make sure you pick the cells with numeric values if you use the Sum function.
  2. Specify if the add-in needs to count the selected colored cells by their fillor font.
    • Select the Background radio button to sum by the fill color.
    • Choose the Font radio button to total the selected values by their text color.
  3. If you want the colored cells to be automatically checked each time you select a new range, check the Recalculate when selection changes option on the add-in pane.
  4. Click the Calculate button to see the results grouped by function in the add-in pane. If you select to sum by color of the background, you will get the totals for each fill color. If you tick the Font radio button, the add-in will display sums for each text color in the highlighted range.
  5. Switch to the function that interests you most: Sum, Count, Average, Max, or Min.
See the number of cells of the same fill color

This handy tool also lets you paste the resulting numbers from the add-in pane to any location in your Excel spreadsheet. Click Paste results…  to see the dialog box where you can choose the top left cell in your current or new worksheet and paste calculation results by the selected function.

Featured customers featured customers
Contact us
  Publish your message on our support forum

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard
Excel add-ins and Outlook tools -