Count & Sum by Color for Microsoft Excel
Count and sum cells by font or background color in Excel 2010, 2013 - 2003
With Sum and Count by Color for Excel you can easily calculate cell data based on their background or font color. This helpful tool lets you:
You may also be interested in Count & Sum by Color app for Mac.
Excel: count colored cells in your worksheet
When you deal with huge Excel tables you may need to change the font or fill shade for distinction and further work like counting cells by color. Suppose you conducted a survey and imported the results into Excel for analysis and graphing. First off, it's necessary to count the number of yes and no answers. All yeses and no's were conditionally formatted using blue and green fill for better visualization. The table contains hundreds of responses from hundreds of interviewees, and your client is extremely impatient about getting the results.
Counting colored cells in Excel can hardly be faster than with the Sum and Count tool. Just select the range with the highlighted values, define if you use Fill or Font color, select the Count function and click Calculate.
You will see the results on the Sum and Count by Color pane beautifully laid out for you just next to your worksheet. This tool will be equally helpful whether you need to count colored cells in Excel with numeric or text data.
Automatically sum colored cells in Excel
Often the highlighted cells contain numeric data like prices or quantities. Thus summing cells in Excel by color became one of the most popular spreadsheet tasks.
For example, you have a large table where the price cells are colored depending on their status. Confirmed prices are green, semi-confirmed are yellow and unconfirmed are blue. You need to sum all prices within each category. In the past one of the solutions was sorting and applying Excel sumif color formula. Today you have the Sum and Count by Color tool that easily adds up all of the values with the same font or fill even if they are scattered throughout your table.
Simply install the add-in, choose the Excel Sum option from the Choose function drop-down list and define other settings. You will see the total for all identically colored cells in a snap. This little utility is a nice time saver in Excel if summing by color is your routine task.
Count and sum cells based on the color you select
Calculate Excel cells by background or font color and paste the results to your table
Applying different fill and font colors make your worksheet look friendlier for colleagues and customers. Some users prefer to highlight data by changing the background color of the cells. If you need a more moderate table in terms of color, you can choose to modify the font only. Whichever option you pick, the Excel Sum and Count by Color add-in will help you. On its pane you will see two radio buttons: Background and Font. Just select the one you use in your spreadsheet for calculating colored cells.
When you are done with counting or summing data by color, you can paste the results you see on the add-in pane to any location in your table. Just press the Paste results… button and pick the destination for the numbers in Excel. This option proved really helpful if you sum or count cells of the same color every day. It allows you to have the results at hand like on the screenshot below:
Choose one of Excel functions for calculating colored cells
Besides Sum and Count, you can choose other common functions for calculating cells in Excel:
- Pick Average from the Functions drop down list to define the average of the values in colored cells.
- Use Max or Min to define the highest and the lowest cell values within the highlighted data.
If you are fond of using the Fill and Font color options for highlighting cells with numeric or text values, the Sum and Count by Color tool will become a nice addition to your Excel. It will quickly add and count colored cells, recalculate them as the range changes, and let you paste the results to any location on your worksheet.
How to count and sum cells by color in Excel:
This add-in comes as part of Ultimate Suite - 50+ professional tools for Microsoft Excel
Download and try Ultimate Suite without any limitations for 14 days.
- Microsoft Excel 2016 (32- and 64-bit)
- Desktop Excel included in Office 365
- Microsoft Excel 2013 (32- and 64-bit)
- Microsoft Excel 2010 (32- and 64-bit)
- Microsoft Excel 2007
- Microsoft Excel 2003
- Windows 10 (32- and 64-bit)
- Windows 8 (32- and 64-bit)
- Windows 7 (32- and 64-bit)
- Windows Vista (32- and 64-bit)
- Windows XP (32-bit)
- Windows Server 2003, 2008, 2012
- .NET Framework 4.0 must be installed.
If you need to install the add-in for a group of users, or on a Terminal Server (servers farm) please see Corporate deployment