Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color. In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.

**Count and Sum cells based on specific fill color by Filter and SUBTOTAL****Count and Sum cells based on specific fill color by GET.CELL function****Count and sum cells based on specific fill color with User Defined Function****Count and Sum cells based on specific fill color with Kutools Functions****Count and Sum cells based on specific fill color (or conditional formatting color) with Kutools for Excel**

#### **Count and Sum colored cells by Filter and SUBTOTAL**

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column. In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel.

**1**. Select blank cells to enter the SUBTOTAL function.

- To count all cells with the same background color, please enter the formula
**=SUBTOTAL(102, E2:E20)**; - To sum all cells with the same background color, please enter the formula
**=SUBTOTAL(109, E2:E20)**;

**Note**: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.

**2**. Select the header of the table, and click **Data **> **Filter**. See screenshot:

**3**. Click the Filter icon in the header cell of the Amount column, and click** Filter by Color **and the specified color you will count by successively. See screenshot:

After filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. See screenshot:

**Note**: This method requires the colored cells you will count or sum are in the same column.

#### One click to count, sum, and average colored cells in Excel

With the excellent ** Count by Color** feature of **Kutools for Excel**, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color.

**Full Feature Free Trial 30-day!**

**Kutools for Excel **- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!** Get It Now**

#### **Count or Sum colored cells by GET.CELL function**

In this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Please do as follows:

**1**. Click **Formulas** > **Define Name**. See screenshot:

**2**. In the New Name dialog, please do as below screenshot shown:

(1) Type a name in the Name box;

(2) Enter the formula **=GET.CELL(38,Sheet4!$E2) **in the Refers to box (**note**: in the formula, *38* means return the cell code, and *Sheet4!$E2* is the first cell in the Amount column except the column header which you need to change based on your table data.)

(3) Click the **OK **button.

**3**. Now add a new Color column right to the original table. Next type the formula **=NumColor** , and the drag the AutoFill handle to apply the formula to other cells in the Color column. See screenshot:

**Note**: In the formula, *NumColor* is the named range we specified in the first 2 steps. You need to change it to the specified name you set.

Now the color code of each cell in the Amount column returns in the Color Column. See screenshot:

**4**. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown:

A. To count cells by color, please enter the formula **=COUNTIF($F$2:$F$20,NumColor)**;

B. To sum cells by color, please enter the formula **=SUMIF($F$2:$F$20,NumColor,$E$2:$E$20)**.

**Note**: In both formulas, *$F$2:$F$20 *is the Color column, *NumColor* is the specified named range, *$E$2:$E$20* is the Amount Column, and you can change them as you need.

Now you will see the cells in the Amount column are counted and sum by their fill colors.

#### ** Count and sum cells based on specific fill color with User Defined Function**

Supposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Here, this method will introduce a VBA to solve the problem.

**1**. Hold down the **ALT + F11** keys, and it opens the **Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** >** Module**, and paste the following code in the Module Window.

**VBA: Count and sum cells based on background color:**

`Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)Dim rCell As RangeDim lCol As LongDim vResultlCol = rColor.Interior.ColorIndexIf SUM = True ThenFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = WorksheetFunction.SUM(rCell, vResult)End IfNext rCellElseFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = 1 + vResultEnd IfNext rCellEnd IfColorFunction = vResultEnd Function`

**3**. Then save the code, and apply the following formula:

A. Count the colored cells:** =colorfunction(A,B:C,FALSE)**

B. Sum the colored cells:** =colorfunction(A,B:C,TRUE)**

Note: In above formulas, **A** is the cell with the particular background color you want to calculate the count and sum, and **B:C** is the cell range where you want to calculate the count and sum.

**4**. Take the following screenshot for example, enter the formula**=colorfunction(A1,A1:D11,FALSE)** to count the yellow cells. And use the formula **=colorfunction(A1,A1:D11,TRUE)** to sum the yellow cells. See screenshot:

**5**. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:

#### ** Count and Sum cells based on specific fill color with Kutools Functions**

Kutools for Excel also supports some useful functions to help Excel users to make special calculations, says count by cell background color, sum by font color, etc.

**Kutools for Excel **- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!** Free Trial Now!**

**1**. Select the blank cell you place the counting results, and click **Kutools** > **Kutools Functions** >** Statistical & Math** > **COUNTBYCELLCOLOR**. See screenshot:

**2**. In the Function Arguments dialog, please specify the range you will count colored cells within in the** Reference** box, choose the cell that is filled by the specified background color in the **Color_index_nr** box, and click the **OK** button. See screenshot:

**Notes:**

(1) You can also type the specified Kutools Function **=COUNTBYCELLCOLOR($A$1:$E$20,G2)** in the blank cell or formula bar directly to get the counting results;

(2) Click **Kutools** > **Kutools Functions** > **Statistical & Math** > **SUMBYCELLCOLOR** or type **=SUMBYCELLCOLOR($A$1:$E$20,G2)** in the blank cell directly to sum cells based on the specified background color.

Apply the **COUNTBYCELLCOLOR **and **SUMBYCELLCOLOR** functions for each background color separately, and you will get the results as below screenshot shown:

**Kutools Functions** contain a number of built-in functions to help Excel users calculate easily, including Count / Sum / Average Visible cells, Count / Sum by cell color, Count / Sum by font color, Count characters, Count by font bold, etc. Have a Free Trial!

#### ** Count and Sum cells based on specific fill color with Kutools for Excel**

With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have **Kutools for Excel**’s **Count by Color** utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.

**Kutools for Excel **- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!** Free Trial Now!**

**1**. Select the range that you want to use, and click **Kutools Plus** > **Count by Color**, see screenshot:

**2**. And in the **Count by Color** dialog box, please do as below screenshot shown:

(1) Select **Standard formatting** from the **Color method** drop down list;

(2) Select **Background **from the **Count type** drop down list.

(3) Click the Generate report button.

**Note**: To count and sum colored cells by specific conditional formatting color, please select** Conditional formatting** from the **Color method** drop down list in above dialog, or select** Standard and Conditional formatting **from the drop down list to count all cells filled by the specified color.

Now you will get a new workbook with the statistics. See screenshot:

The Count by Color feature calculates (Count, Sum, Average, Max, etc.) cells by background color or font color. Have a Free Trial!

#### Related article:

How to count / sum cells based on the font colors in Excel?

#### ** Demo: Count and sum cells based on background, conditional formatting color: **

**Kutools for Excel** includes more than 300 handy tools for Excel, free to try without limitation in 30 days. **Download and Free Trial Now**!

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

**Read More... Free Download... Purchase...**

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

**Read More... Free Download... Purchase...**

## FAQs

### How to count and sum cells based on background color in Excel? ›

Insert the code of the CountCellsByColor function in your workbook. In a cell where you want the result to appear, start typing the formula: **=CountCellsByColor(** **For the first argument, enter the range in which you want to count colored cells.** **For the second argument, supply the cell with the target color**.

**How do you sum count cells by fill or background color in Excel? ›**

Insert the code of the CountCellsByColor function in your workbook. In a cell where you want the result to appear, start typing the formula: **=CountCellsByColor(** **For the first argument, enter the range in which you want to count colored cells.** **For the second argument, supply the cell with the target color**.

**Can you do a Countif formula based on cell color? ›**

To do that you need to create a custom function using VBA that works like a COUNTIF function and returns the number of cells for the same color. You will follow the syntax: =CountFunction(CountColor, CountRange) and use it like other regular functions. Here CountColor is the color for which you want to count the cells.

**Can I use Countif to count colored cells conditional formatting? ›**

**The COUNTIF formula counts the number of cells in the named range corresponding to the background color**. Review the number is gives you. Repeat this process for any other colors you want to count.

**Can you do a Countif on conditional formatting? ›**

If you want to highlight duplicate cells or entire rows containing duplicate entries, **you can create conditional formatting rules based on the COUNTIF formulas**, as demonstrated in this tutorial - Excel conditional formatting formulas to highlight duplicates.

**How do I use Countif with conditional formatting in Excel? ›**

**To highlight the winning numbers:**

- Select cells C6:H8.
- On the Ribbon's Home tab, click Conditional Formatting, then click New Rule.
- Click Use a Formula to Determine Which Cells to Format.
- For the formula, use the CountIf function: ...
- Click the Format button.
- Select formatting options (green fill, in this example), click OK.

**Can you do a Countif based on text color? ›**

**By using the COUNTIF function or conditional formatting, you can easily count the number of cells with a specific text color**.

**How to do a VLOOKUP based on cell color? ›**

Click Home > Conditional Formatting > Add New Rule.

In the New Formatting Rule dialog box, click Use a formula to determine which cells to format. Under Format values where this formula is true, type the formula: “=VLOOKUP(B3,$H$3:$I$10,2,FALSE) < D3” Click Format. In the Color box, select Red.

**Is there a formula to color cells in Excel? ›**

In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field: **=IsBlank()**- to change the background color of blank cells.

**Can you subtotal by color in Excel? ›**

Excel helps us to subtotal by color in an efficient manner. We can **use the SUMIF function to perform subtotal by color in Excel**. It helps to add cells based on numbers, dates, and text matching certain criteria.

### How do sum ifs work? ›

The SUMIFS function, one of the math and trig functions, **adds all of its arguments that meet multiple criteria**. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value.

**How do I extract only colored cells in Excel? ›**

On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click the criteria.

**How do I sum in Excel without counting hidden cells? ›**

Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.

**Can you do an if statement in sheets based on color? ›**

In Google Sheets, **conditional formatting allows you to dynamically change a cell's text style and background color based on custom rules you set**. Every rule you set is created using an if this, then that statement.

**How do I count filled cells in Excel? ›**

**On the Formulas tab, click More Functions, point to Statistical, and then click one of the following functions:**

- COUNTA: To count cells that are not empty.
- COUNT: To count cells that contain numbers.
- COUNTBLANK: To count cells that are blank.
- COUNTIF: To count cells that meets a specified criteria.

**Can you do a subtotal Countif? ›**

The SUBTOTAL function can easily generate sums and counts for visible rows. However, **SUBTOTAL is not able to apply criteria like the COUNTIFS function without help**. Conversely, COUNTIFS can easily apply criteria, but is not able to distinguish between rows that are visible and rows that are hidden.

**How do you use Countif with text condition? ›**

To count the cells with text in Excel, choose a destination cell and enter the formula **=COUNTIF(range,criteria)**. Here, the range denotes the array of cells within which you want the function to act. The criteria variable denotes the condition to satisfy when counting the values.

**How do I format cells in Excel with color based on text? ›**

**Apply conditional formatting based on text in a cell**

- Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
- Click HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains. ...
- Select the color format for the text, and click OK.

**How do you use Countif formula for cells with text? ›**

Data | Data |
---|---|

=COUNTIF(A2:A5,"*") | Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4. |

**What is the difference between VLOOKUP and Xlookup? ›**

To recap, the key differences between an XLOOKUP and a VLOOKUP are: **XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column**.

### Can you use Xlookup in conditional formatting? ›

**Using XLOOKUP in a Conditional Formatting statement in Excel 365 will not function as expected when the document is Exported/Downloaded as PDF**.

**How does Xlookup work in Excel? ›**

The XLOOKUP function **searches a range or an array, and then returns the item corresponding to the first match it finds**. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

**Can Excel sort data by color? ›**

Sort by cell color, font color, or icon

Select a cell in the column you want to sort. On the Data tab, in the Sort & Filter group, click Sort. In the Sort dialog box, under Column, in the Sort by box, select the column that you want to sort. **Under Sort On, select Cell Color, Font Color, or Cell Icon**.

**How do I segregate data in Excel by color? ›**

**Right-click the lowest value , point to Sort, and then select Sort By Selected Cell's Color**. Right-click the highest value, point to Sort, and then select Sort By Selected Cell's Color.

**How do I conditional format subtotal in Excel? ›**

**Conditional Formatting with Subtotals in Excel**

- Step 1: Selecting the Data Range. Highlight all of the columns containing data. ...
- Step 2: Create a New Conditional Format Based on a Formula. On the Home tab, click the Conditional Formatting button and select New Rule…
- Step 3: Apply Color Scheme.

**What is the difference between Sumifs and IFS? ›**

The only difference between Excel SUMIFS & SUMIF functions is that **SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time**.

**What is the difference between functions sum and Sumif? ›**

The SUM function totals one or more numbers in a range of cells. SUMIF FUNCTION -The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000). The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function.

**How do you sum cells with functions? ›**

If you need to sum a column or row of numbers, let Excel do the math for you. **Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter**, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

**How do I fill cells in Excel with values based on selected cells? ›**

Select the cell with the formula and the adjacent cells you want to fill. **Click Home > Fill, and choose either Down, Right, Up, or Left**. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.

**How do I select cells based on fill color? ›**

In a range of cells or a table column, click a cell that contains the cell color, font color, or icon that you want to filter by. in the column that contains the content that you want to filter. **Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click a color**.

### How to have Excel automatically fill in values of a pattern based on an example? ›

**If you establish a pattern by typing the full name in column C, Excel's Flash Fill feature will fill in the rest for you based on the pattern you provide**. Enter the full name in cell C2, and press ENTER. Go to Data > Flash Fill, or press Ctrl+E. Excel will sense the pattern you provided in C2, and fill the cells below.

**How do I fill a cell based on condition in Excel? ›**

**Apply conditional formatting based on text in a cell**

- Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
- Click HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains. ...
- Select the color format for the text, and click OK.

**How do I make a number sequence based on the criteria in Excel? ›**

**Fill a column with a series of numbers**

- Select the first cell in the range that you want to fill.
- Type the starting value for the series.
- Type a value in the next cell to establish a pattern. ...
- Select the cells that contain the starting values. ...
- Drag the fill handle.

**How do I select only filled cells? ›**

**Press CTRL+HOME to select the first cell on the worksheet or in an Excel list**. Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.

**How do you select cells that are filled? ›**

**Press CTRL+A**. Note If the worksheet contains data, and the active cell is above or to the right of the data, pressing CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.

**What tool do you use to fill a cell with color in Excel? ›**

You can use Excel's AutoFill feature to fill the cell colors quickly. Simply drag the fill handle to the cells you want to fill and select the cell that contains the color you want to use. You'll be able to see the cells highlight as you drag. The color will fill the cells when you release the mouse button.