Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Using a function to count / sum cells by fill or font color in Excel

In Excel, we always format cells with filling color or font color to make the data more outstanding. And sometimes, we need to count or sum the cells based on background color or font color. That is to say, to sum or count the cells which have the same color. Unfortunately, there is no direct way for us to deal with this task in Excel. But, with the help of Kutools for Excel’s Super Functions, this problem will be solved quickly and easily.

COUNTBYCELLCOLOR function: Counting number of cells by a certain fill color

COUNTBYFONTCOLOR function: Counting number of cells by a certain font color

COUNTSHADES function: Counting numbers of cells which have been filled color

SUMBYCELLCOLOR function: Sum cells by a certain fill color

SUMBYFONTCOLOR function: Sum cells by a certain font color


Click Kutools >> Kutools Functions >> Statistical & Math, see screenshot:

shot count sum cells by color 1


COUNTBYCELLCOLOR function: Counting number of cells by a certain fill color

Supposing you have the following data range which are formatted with some background colors, you want to count the cells based on certain filled color as this:

shot-super-function-2

1. Click a cell where you want to put the result.

2. Then apply COUNTBYCELLCOLOR utility by clicking Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR, see screenshot:

shot count sum cells by color 1 2

3. And in the popped out Function Arguments dialog box, click shot-super-function-0 button besides Reference to select the data range which you want to count the cells by color, and then click the second shot-super-function-0 button besides Color_index_nr to select one color cell that you want to count based on, see screenshot:

shot-super-function-4

4. Then click OK, and the number of yellow color cells have been counted. See screenshot:

shot-super-function-5

5. Repeat above steps to count other color cells as you want.


COUNTBYFONTCOLOR function: Counting number of cells by a certain font color

If you want to count the number of cells by a certain font color, please do as this:

1. Select a cell where you want to put the result.

2. Then click KutoolsKutools Functions > Statistical & Math > COUNTBYFONTCOLOR, see screenshot:

shot count sum cells by color 1 3

3. In the popped out Function Arguments dialog box, click shot-super-function-0 button besides Reference to select the data range which you want to count the cells based on a certain font color, and then click the second shot-super-function-0 button besides Color_index_nr to select one cell contains the font color you want to count based on, see screenshot:

shot-super-function-7

4. And then click OK, all the cells of the same font color have been counted, see screenshot:

shot-super-function-8

5. To count the number of other font colors, you just need to repeat the above step by step.


COUNTSHADES function: Counting numbers of cells which have been filled color

To get the number of all fill colored cells, the COUNTSHADES function of Super Functions also can help you.

1. Click one cell that you want to put the result.

2. Then click Kutools > Kutools Functions > Statistical & Math > COUNTSHADES, see screenshot:

shot count sum cells by color 1 4

3. In the Function Arguments dialog box, click shot-super-function-0 button besides Reference to select the data range which you want to count the colored cells, see screenshot:

shot-super-function-10

4. Then click OK to close the dialog, and all the color filled cells are counted as follows:

shot-super-function-11


SUMBYCELLCOLOR function: Sum cells by a certain fill color

With the above functions, you can quickly count the number of cells based on a certain color, and in this section, I will talk about summing cells by a specific filled color.

1. Click one cell that you want to sum the cells with a specific fill color.

2. Then apply this function by clicking Kutools > Kutools Functions > Statistical & Math > SUMBYCELLCOLOR, see screenshot:

shot count sum cells by color 1 5

3. In the popped out Function Arguments dialog box, click shot-super-function-0 button besides Reference to select the data range which you want to sum the cells based on a specific color, and then click the second shot-super-function-0 button besides Color_index_nr to select one cell contains the filled color you want to sum based on, see screenshot:

shot-super-function-13

4. And then click OK to close the dialog, and all the cells which filled with the specific color you select have been summed.

shot-super-function-14

5. To sum the cells of other filled colors, please repeat above steps one by one.


SUMBYFONTCOLOR function: Sum cells by a certain font color

This powerful Super Functions also supports a function to sum cells by a certain font color as you need.

1. Click one cell that you want to sum the cells with a specific font color.

2. Then apply this function by clicking KutoolsKutools Functions > Statistical & Math > SUMBYFONTCOLOR, see screenshot:

shot count sum cells by color 1 6

3. In the Function Arguments dialog box, click shot-super-function-0 button besides Reference to select the data range which you want to sum the cells based on a font color, and then click the second shot-super-function-0 button besides Color_index_nr to select one cell contains the font color you want to sum based on, see screenshot:

shot-super-function-16

4. Then click OK, all the cell values which have the same font color you specify have been added up. See screenshot:

shot-super-function-17

5. Please repeat the above steps to sum cell values of other font colors as you need.


Demo: Count / sum cells by fill or font color in Excel

Kutools for Excel: with more than 200 handy Excel add-ins, 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.

Be the first to comment.