Sum and count by color in Excel

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 tool to sum and count the colored data in your spreadsheet.

Video: How to work with Count and Sum by Color

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.

Start Sum by Color

Open the Excel worksheet where you need to count cells by color and click the Sum by Color icon in the Calculate group on the Ablebits Tools tab:

Start Sum by Color.

Pick one of two tools in the drop-down list:

Sum and count by one color

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

After picking the first option, you will see the Count and Sum by One Color pane.

How to count and sum by one color in Excel.

  1. By default, the add-in picks the entire data range in your Excel worksheet. You can change it by selecting the needed table or entering the range address manually.
  2. Also, you can get the entire table selected automatically. For this, pick any cell in the table and click the Expand selection icon.
  3. Click the Color Picker icon and select a cell that represents the background and/or font color you want to sum and count by.
  4. Click the Calculate button and get the result on the add-in's pane.
    Note. If there are empty cells in your range, the tool will identify the font and fill colors that are set in the Format Cells option.
  5. To see calculations by background color, go to the Background tab.
  6. If you want to see the count by font color, open the Font tab.
  7. If needed, you can paste a single calculation result to your table: just hover the mouse cursor over the needed function (SUM, COUNT, MAX, etc.) and click the Paste button. The add-in will ask you to pick the top-left cell for the result:

    Paste the result of Sum by One Color calculations.

    Note. If you use this option, the numerical result will be pasted into your table without any text labels. For your convenience, the resulting cell will have the same font/background color as the cells you counted.
  8. Click the Paste All... button to insert all the results to the place you choose in your sheet. You will see the same dialog box to specify the top-left cell for the count color results.
    Note. The results will have text labels and will be colored the way the cells you counted:

    Sum and count by one color and paste the result.

Sum and count by all colors

This tool will give you a quick overview of color-coded data in your table. For example, if you use different hues to highlight prices from the vendors, the utility will 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 run Sum and Count by All Colors:

Start Sum by All Colors.

The add-in selects the entire data range in your worksheet automatically. You can change the range by entering the new address manually or selecting it in you sheet.

When the range is chosen, click Calculate and see the results straight on the pane:

Sum and Count in Excel by all colors.

  1. Select Background to see the result counted by the cell's fill color.
  2. Choose Font to total the selected values by their text color.
  3. Open this drop-down menu to switch to the function that interests you most: Sum, Count, Counta, Average, Averagea, Max, or Min.
  4. This handy tool also lets you paste the resulting numbers from its pane to any location in your Excel worksheet. Click Paste All... and you will see a dialog box where you need to specify the top-left cell in your current or new worksheet. Once the cell is chosen, click OK to paste calculation results by the selected function.
    Note. The calculation result is inserted into your worksheet without any text labels. The color of the cell with the result represents all the cells that were counted or summed.

Sum by colors in Excel.

Note. Sum and Count by Color does not identify the format of the values in the cells. For example, if you have dates in your range, the tool will treat them as numbers.

Responses

Vedant Pandey says:
July 20, 2018 at 5:55 pm

It was very good and the video showed how to count by color. It was my first time so it was good

Reply

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.