﻿ How to quickly count and sum colored cells in Excel

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

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

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.

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.