How to Count and Sum Colored Cells

In Excel for Mac

Count & Sum by Color is a nifty app for Mac that quickly calculates cells by the color of background or font. From this help page, you'll learn how to use the All Colors and One Color tabs on the app's pane to sum or count highlighted cells, as well as find average, min and max values based on the color code. Below you'll also find how to paste the calculation results to any location in your table.

How to work with the All Colors tab

Count & Sum by Color app will become your reliable assistant in adding or counting cell values grouped by colors. If you are fond of using different color codes to highlight certain parts of data, this helpful app will show you Sum, Count, Average, Min or Max for the colored cells.

  1. If you need to sum or count all highlighted cells in Excel 2011, first open the worksheet with your data and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab:
    Click on Count and Sum by Color name under the Ablebits.com tab.
  2. You'll see the Count & Sum by Color pane open. Click on the All Colors tab:
    Click on the All Colors tab.
  3. Highlight the needed range in your table and you will see its address in the Select range field:
    Highlight a range to see its address in the Select range field.
    It's also possible to click on the Select range button to pick the necessary area manually:
    Click on the Select range icon to pick the necessary area manually.
  4. Now you can specify the color option by selecting the Background or Font radio button:
    Specify the color option by selecting the Background or Font radio button.

    • Press the Background radio button to calculate by the color of cells.
    • Select the Font radio button to process the selected values by their text color.
  5. Press the Calculate button to process the range:
    Click on the Calculate button.

    The results will be displayed on the Count & Sum by Color pane grouped by color codes:
    See the results on the app's pane grouped by colors.

  6. The app will quickly perform all math operations on your data. You can then pick any function from the Show results for drop-down list to see the needed calculation results:
    Pick any function from the Show results for drop-down list.

    • Sum - add all the cell values.
    • Count - count the number of cells.
    • Average - determine the average of the values.
    • Min - see the highest number.
    • Max - show the lowest cell value.
  7. This handy calculating tool also lets you paste the results from the app's pane to any location on your spreadsheet.

    Click on the Paste results... button to see the Ablebits.com Count & Sum by Color dialog box:
    Click on the Paste results... button.
    See the Count & Sum by Color dialog box.
    Select any cell in your current or new worksheet for adding the numbers and click OK to see the calculation results added:
    See the calculation results added.

How to work with the One Color tab

If you use multiple different colors to group and highlight data in your Excel 2011 tables, you may need to pick a certain color and use it to calculate cell values. The One Color tab on the Count & Sum by Color pane lets you instantly calculate cell data based on the color you select.

  1. Open the Excel table where you need to calculate cell values and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab:
    Clicking on Count and Sum by Color name under the Ablebits.com tab.
  2. Click on the One Color tab to start:
    Click on the One Color tab.
  3. Select the range in your table and you will see its address in the Select range field:
    Highlight a range to see its address in the Select range field.
    You can also click on the Select range button to pick the necessary range manually:
    Click on the Select range icon to pick the necessary area manually.
  4. Now you need to specify the correct color by choosing the cell that contains the necessary fill or font in your table. To do this, click on the Select range button next to the Pattern cell field:
    Click on the Select range icon next to the Pattern cell field.
    You'll get the Ablebits.com Count & Sum by Color dialog box that lets you select a pattern cell with the right color:
    Select a cell with the right color.
    The address of the selected cell will automatically appear in the Pattern cell field.
  5. Define if you want to use the Background or Font color for calculation:
    Specify the color option by selecting the Background or Font radio button.

    • Click the Background radio button to count the selected data by the fill color.
    • Select Font if it's the text color you want to use.
  6. Finally, press the Calculate button:
    Click on the Calculate button.
    On the add-in pane you'll instantly see Sum, Count, Average, Min and Max calculated for the cell data of the specified color:
    See Sum, Count, Average, Min and Max calculated for the cells of the specified color.
  7. If you want to paste the results to your table, just click on the Paste results... button:
    Click on the Paste results... button.
    You'll get the Ablebits.com Count & Sum by Color dialog box letting you pick the top left cell for pasting the calculations. Just click on the needed cell and click OK on the dialog box:
    See the Count & Sum by Color dialog box.
    Feel free to use the One Color tab each time you need to see the results just for the color you specify.

    The Count & Sum by Color app will save you much time on calculating and recalculating highlighted data in your Excel for Mac. Besides summing and counting values in the colored cells, it also allows applying Average, Max or Min functions to the highlighted range. Choose this tool for quick and reliable color calculations.

Responses

Le says:
January 7, 2023 at 3:04 pm

Dear Sir/Madam,

I use Count & Sum by Color app a lot on my old Mac. Recently o got a new one but I can not find the app from App Store or website or Text toolkit for Excel for Mac.
Is it possible to guide me on how to get it back so that I can use this app on my Mac ?

Regards,
Le

Katerina Bespalaya (Ablebits Team) says:
January 7, 2023 at 7:17 pm

Dear Le,

Thank you for your comment.

Unfortunately, the Count & Sum by Color app is no longer available in the App Store since the product is discontinued.

If you'd like to try out the Text Toolkit add-in, feel free to install it right from the Microsoft Store. Our online docs will help you quickly start using the tools. The entry point is here: https://www.ablebits.com/docs/excel-text-toolkit-intro/

Also, you can have a look at Function by Color for Google Sheets that can be run in a browser on your Mac. The add-on is installed and purchased directly through Google Sheets. You can test it out for 30 days for free to see if it suits your needs: https://workspace.google.com/marketplace/app/function_by_color/431807167189

Please contact us back if you have any other questions or need further assistance.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

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.