Tip: Other languages are Google-Translated. You can visit the English version of this link.

Kutools for Excel 20.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 11.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

Quickly calculate cells value based on font,background or conditional color in Excel

There are many formulas to perform data calculation in Excel. But if you want to count or sum cells by their fill or background color or font color, and do other calculations with a range of cells based on a specified background, font, conditional formatting color, there is no such formula for you to do so in Excel. But Kutools for Excel's Count by Color utility can quickly apply calculations to cells based on cell color and generate a report in a new workbook.

Apply calculation to cells based on cell color and generate a report in new workbook

Apply count/sum calculations to cells based on font color in Excel

Enable you to do smarter, faster and better

Kutools - the best office productivity tool brings 300 advanced features to Excel. Details... Download...

Office Tab - brings tabbed interface to Excel and Word, make your work much easier. Details...Download...


Click Kutools Plus >> Count by Color. See screenshots:

shot advanced sort 1 1

Apply calculation to cells based on cell color and generate a report in new workbook

Supposing you have a worksheet containing a range of cells as shown in the below screenshot, and you want to apply calculations (count, sum, etc.) to the range based on their background or shading color. You can do it as follows.

1. Please select the range that you want to apply calculations based on their background / shading color, and apply the Count by Color utility (Click Kutools Plus > Count by Color).

2. In the opening Count by Color dialog,

(1) Click the Color method box and specify one of options from the drop down list;

(2) Click the Count type box and specify the Background from the drop down list.

A: Count: it will calculate how many cells with specific cell color in the range.

B: Sum: it will add all the number in the cells with specific cell color in the range.

C: Average: it will return the average of the cells with specific cell color in the range.

D: Max: displaying the Max value of the cells with specific cell color in the range.

E: Min: displaying the Min value of the cells with specific cell color in the range.

Tips:

There are three options in the drop down list of Color method: Standard formatting, Conditional formatting, and Standard and Conditional formatting.

If you need to calculate cells by fill/background color which are formatted with Home > Fill Color, please select Standard formatting; if you need to calculate cells by fill/background color which are formatted by Conditional Formatting, please select Conditional formatting; if calculating all cells by fill/background color, please select the Standard and Conditional formatting.

3. If you want to generate the report in a new workbook, please click Generate report button. And you will see the report in the new workbook as follows:


Apply count/sum calculations to cells based on font color in Excel

For example, you have a range of cells formatted by different font colors as below screen shot shown, and you want to calculate (count, sum, average, or others) the cells by their font colors, you can apply the Count by Color utility as following:

1. Select the range where you will calculate cells by font colors, and apply the Count by Color utility (Click Kutools Plus > Count by Color).

2. In the opening Count by Color dialog box:

(1) Click the Color method box and specify one of options from the drop down list based on your needs, such as Standard and Conditional formatting;

(2) Click the Count type box, and specify Font from the drop down list.

3. If you want to generate the report in a new workbook, please click the Generate report button. Then you will see the report in a new workbook as following screen shot shown:


Notes:

1. This utility will apply the calculations to the cells containing numeric value based on their cell color except the Count calculation.

2. The conditional formatting color can be applied in Excel 2010 and later version. In Excel 2007, you can only apply the font and background color with standard formatting,  see screenshot:


Demo: Quickly calculate cells value based on font,background or conditional color in Excel

Kutools for Excel: with more than 300 handy Excel utilities, free to try with no limitation in 60 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sankar · 4 years ago
    Dear sir/madam
    Thanks for your above. kindly suggest how to get avg, max, min values.
    Thanks & Regards
    Sankar
  • To post as a guest, your comment is unpublished.
    JanToo · 5 years ago
    Same problem here as Dean.
    Tested in new workbook (Excel 2010) and is not working properly.
    Please fix in next update.
  • To post as a guest, your comment is unpublished.
    Dean · 5 years ago
    The count by color is not working for my data table. It is working for a two row & tow column only. It is not calculating the entire data table results
    • To post as a guest, your comment is unpublished.
      Admin_jay · 4 years ago
      [quote name="Dean"]The count by color is not working for my data table. It is working for a two row & tow column only. It is not calculating the entire data table results[/quote]
      We will enhance this feature in upcoming versions.

      It cannot calculate the conditional formatting colors right now.

      It cannot process the table data right now.
  • To post as a guest, your comment is unpublished.
    iustin · 5 years ago
    count by color is not working. It sais "calculating"and then nothing hapens - the report is not displayed.
    • To post as a guest, your comment is unpublished.
      Jan van IJsseldijk · 4 years ago
      [quote name="iustin"]count by color is not working. It sais "calculating"and then nothing hapens - the report is not displayed.[/quote]
      In one worksheet I have a range with A2:A15 with only numbers and colors. An empty result is shown. On another range I get a result. I have a pretty big sheet with the same things and I want to use Kutools, but now very hesitating on buying it.
    • To post as a guest, your comment is unpublished.
      adminjay · 5 years ago
      Hello, please try to contact me jaychivo@extendoffice.com.

      Please try to apply the same operations to another workbook.

Feature Tutorials