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:

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:

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:

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

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

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 Kutools > Kutools Functions > Statistical & Math > COUNTBYFONTCOLOR, see screenshot:

3. In the popped out Function Arguments dialog box, click  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  button besides Color_index_nr to select one cell contains the font color you want to count based on, see screenshot:

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

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:

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

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

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:

3. In the popped out Function Arguments dialog box, click 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 button besides Color_index_nr to select one cell contains the filled color you want to sum based on, see screenshot:

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

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 Kutools > Kutools Functions > Statistical & Math > SUMBYFONTCOLOR, see screenshot:

3. In the Function Arguments dialog box, click 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 button besides Color_index_nr to select one cell contains the font color you want to sum based on, see screenshot:

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

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