Sometimes you may use different colored cells in Google Sheets to represent categories. This makes your data easy to read. In such cases, it can be useful to count the cells based on the cell’s color. For instance, we have a list of student names and they’re colored. If the cell is red in color, the student is absent. If the cell is green in color, the student is present.
If you want to know the number of students present, it’d be useful if you could simply count the green-colored cells, right? So we will show you how to do that in easy steps, plus there are four different ways in which you can count the colored cells.
Let us begin!
4 Methods of Counting Colored Cells in MS Excel:
- Using SUBTOTAL and filters
- Using VBA code
- Using the FIND function
- Using COUNTIF and GET.CELL
Method #1: Using SUBTOTAL and Filters
This method uses the SUBTOTAL formula to calculate the total number of cells. You can then use the filter to only see cells of a particular color.
Step #1: Pick a cell to display the count
Select a cell where you want to see the results of counting all the colored cells.
In our example, we’ve chosen C1 to display the count. You can see the selected cell in the Name Box on the top left corner just below the toolbar.
Step #2: Apply the SUBTOTAL formula
Capture the subtotal formula in cell C1.
The formula is [=SUBTOTAL(function_name, ref1)].
function_name is represented by numeric values 1 to 11 and 101 to 111.
For this purpose, you can use either 102 or 103. You can use 102 if you’re counting cells with numeric data. You can use 103 if you’re counting cells with text data.
Click on cell C1.
Go to the Formulas tab in the top menu bar and select Math & Trig, scroll down to SUBTOTAL and click on it.
Now, fill the Function_num and Ref1 fields and click on OK.
For our example, we will use function_num 103 and the Fields are A2 to A20 (A2:A20).
Here, the formula would be =SUBTOTAL(103, A2:A20).
Alternatively, you can directly type the formula into the cell.
Step #3: Press Enter
If you have used the inserted the formula using the menu bar, click on the OK button.
If you have typed in the formula, press Enter to see the formula in action.
You would now see the total count of all the cells.
Note: The SUBTOTAL formula with function numbers between 101 and 111 calculates visible cells. If you use another formula, it will count the number of cells regardless if they are visible or not.
Step #4: Apply filter by cell color
Now let’s apply a filter for the green-colored cells to get the count of students who are present.
Under the main menu Home tab, click Sort & Filter, and then from the dropdown, select Filter.
Alternatively, you can click on the alphabetic header row and press Ctrl+Shift+L.
Once you have applied a filter to your table, you will see a small block with a downward pointing arrow next to each header cell. This is the Filter Icon.
Click on the Filter Icon.
Select Filter by Color.
Click on the color you want to filter and have visible.
In our example, we picked the green color.
Click on the OK button.
You will now only see the green-colored cells in Excel.
In addition, the SUBTOTAL formula in C1 now only gives you the total number of green cells.
Method #2: Using VBA code
In this method, you create a custom or user-defined function. With the created function, you can count the colored cells.
Step #1: Select Visual Basic
Go to the Developer tab in the main menu bar and select Visual Basic.
Step #2: Create a module
You can now see a visual basic application dialog box.
Click on the minus sign at the top left.
Click on Insert and from the submenu that appears, select Module.
Step #3: Copy-paste the VBA code
Once you click on Module, a dialog box will be launched. Copy the following code.
Function Color_Cell_Count(ColorCell As Range, DataRange As Range)
Dim Data_Range As Range
Dim Cell_Color As Long
Cell_Color = ColorCell.Interior.ColorIndex
For Each Data_Range In DataRange
If Data_Range.Interior.ColorIndex = Cell_Color Then
Color_Cell_Count = Color_Cell_Count + 1
End If
Next Data_Range
End Function
Now paste the code into the dialog box.
Close the dialog box by clicking on the red X icon at the top-right corner.
Step #4: Apply the code in your spreadsheet
Color two cells, the same as the source formatting. In our example, that would be red and green.
C2 is red in color and D2 will represent the number of red-colored cells.
C3 is green in color and D3 will represent the number of green-colored cells.
Place your cursor in D2, and enter the formula =Color_Cell_Count(C2, A2:A20).
C2 represents a cell with the color you want to count, while A2:A20 represents the range of cells to be counted.
Step #5: Press Enter
Press Enter to see the count.
Similarly, copy the same formula onto the next cell to find the number of green-colored cells.
Method #3: Using the FIND function
In this method, you use the FIND function to get the number of cells based on formatting. Here, the formatting is colored cells.
Step #1: Select the range of cells
Select the range of cells by clicking and dragging your cursor across them.
The Name Box will show you the selected range.
Step #2: Open ‘Find and Replace’ dialog box
Press Ctrl and the letter F on your keyboard simultaneously to open the Find and Replace dialog box.
Select Options in the dialog box.
Step #3: Go to ‘Format’
Click on the Format button.
This will open the Find Format dialog box.
Step #4: Select ‘Choose Format From Cell’
In the Find Format dialog box, at the bottom you will see the ‘Choose Format From Cell…’ option.
Click on it.
The window will now minimize, and your mouse cursor will change to look like a pen icon.
Tap on any cell with the color that you want to count.
In our example, we pick a green-colored cell.
Step #5: Select ‘Find All’
The Find and Replace window will now open again, where you will see the chosen color in Preview.
Click on the Find All button to find all such cells with the same format.
You will see a list of all the green-colored cells along with their count at the bottom of the window.
Method #4: Using COUNTIF and GET.CELL
In this method, we first get the unique color code and then count the cells using the color code.
Step #1: Go to ‘Formulas’ and click on ‘Define Name’
In the Formulas tab in the main menu bar, click on Define Name.
Select Define Name again from the dropdown that appears.
Step #2: Enter details for Define Name
A new window will appear, with fields to be completed.
In the Name: field, enter any name you like. Here, we’ve entered ColorCode.
Type the formula =GET.CELL(38,Sheet1!$A2) in the ‘Refers to:’ field.
In our example, 38 represents the unique color number in Excel.
The next parameter is the cell reference, i.e. cell A2 in Sheet 1.
Click on the OK button once you’re done.
Step #3: Get the color code
Now go to your Excel sheet.
In the next column, B, in our example, enter =ColorCode.
In your case you would use the name you assigned.
Now, drag the fill handle across all the rows with data. The fill handle is a the little square at the bottom right of your selected cell.
This would ensure you get the code for all your colored cells.
Step #4: Apply the COUNTIF formula
In column D, fill two cells with the same colors as in Column A.
Next you will count the different colors matching the colors in column D.
Place your cursor in column E, next to the colored cell in column D, and enter the formula.
The formula is =COUNTIF(range, criteria).
We use the color code to calculate.
In our example, the range is from B2 to B20.
Criteria is the name we created, ColorCode.
Therefore our formula would become =COUNTIF(B2:B20, ColorCode).
Step #5: Press Enter
Once you’re done entering the formula, press Enter.
This will give you the count of cells with the color in the cell left to it.
Drag the fill handle down to the next cell, to get the count for the other color as well.
The Bottom Line
No more struggling to count the number of colored cells. These four methods will help you get the count of the colored cells in no time!