Count Cells By Color In Excel

Using colors in Excel spreadsheets can make your data more vivid, distinguishable, and searchable. So how can you quickly count cells by color? Buffcom.net will guide you through 3 methods to perform this feature, which will surely be helpful for your work.

Combining the SUBTOTAL Function and Color Filtering Tool

This method is based on 2 extremely useful features of Excel:

• The SUBTOTAL function is a flexible function applied in many different cases: sum, average, count cells,… Here, you use the SUBTOTAL function with the function of counting cells by color to distinguish between cells.
• The color filtering tool in Excel: helps filter cells colored, you can select one or more colors that you want to display.

Using the SUBTOTAL Function to Count Cells by Color

Step 1: Enter the formula =SUBTOTAL(102,C2:C11)

Where:

• 102: equivalent to the COUNT function, used to count cells with color in the data range.
• C2:C11: the range of cells to be counted.

Step 2: Press Enter and get the result.

Using the Filter Tool (Autofilter) to Filter Cells by Corresponding Color.

Step 1: On the Data tab on the toolbar, click Filter to apply the color filter to the data table.

Step 2: Click Filter by Color and select the color you want to filter the data table.

Step 3: After filtering by color, the SUBTOTAL function will count the cells as the following result.

Combining the COUNTIF Function with the GET.CELL Function

Using the GET.CELL Function to Determine the Cell’s Color Code.

Step 1: Click on cell E2. Then, select Define Name in the Formulas tab.

Step 2: The New name dialog box appears.

• Name: enter any name. Here, you choose Maunen.
• Scope: Workbook
• Refers to: enter the formula =GET.CELL(38,Sheet1!$E2)
  Where:
• 38: the parameter determines the background color in the cell.
• Sheet1!$E2: is the starting cell position to determine the color. Here, you fix column C, not fix row 2 (so that the function can be used for other rows in column C).
  Count cells by color

Step 3: Write the formula =Maunen into cell F2 next to cell E2, press OK to display the result. Then, copy the formula for the remaining rows in column F.

Using the COUNTIFS Function to Count Cells by Color Code.

Enter the formula =COUNTIFS(F2:F11,”=40″), then press Enter to display the result.

Do the same with the remaining color codes.

Summary of Counting Cells by Color in Excel

So, you can count cells by color in 2 ways:

• Combining the SUBTOTAL function and the color filtering tool.
• Combining the GET.CELL function and COUNTIFS.

Hope you have gained useful knowledge for your job.Moreover, if you want to explore a product with full features, please contact Buffcom.net!