Hassan Shuvo
Microsoft Excel has a wide range of functions, but none of them can count the number of cells with respect to their color. We can count the cells containing text or numbers, but in terms of counting blank cells, we have some work to do. In this article, we will discuss how you can count the blank and colored cells in particular in Excel with some easy methods.
📁 Download Excel File
Download this file to practice counting blank colored cells in Excel.
Learn to Count Blank Colored Cells in Excel with These 3 Methods
We’ve compiled three different ways for you to count blank colored cells. One is a built-in feature, another is using a function, and the last is VBA code. For illustration purposes, we will keep the dataset as simple as possible. We colored the blank cells in advance, and next, we will count them.
1. Using Find & Select Feature
The first method we will show you is a built-in feature of Excel. We will use the Find & Select feature first to find the blank cells and then count them based on a particular color.
⬇️⬇️ STEPS ⬇️⬇️
- First, you will select our data range as we selected B6:E14.
- Then, from the Home tab you will see Find & Select in the Editing From the context menu select Go To Special. By doing this, the Go To Special dialogue box will appear.
- Click OK after selecting Blanks from the radio button. This will isolate the blank cells from the whole selection.
- Next, again from Find & Select, select Find. The Find and Replace dialogue box will then be displayed.
- Expand the Find tab by clicking Options.
- Following that, open the Find Format dialog box by selecting Format.
- Then, in the Fill tab, choose the cell color you want to count and press OK.
- Last but not least, click Find All in the Find and Replace box.
As you can see, the blank and selected color cells have been listed at the bottom.
2. Employing GET.CELL and COUNTIF Functions
Another way we can count the blank colored cells is by using the GET.CELL and COUNTIF functions separately. GET.CELL can return more details about the worksheet environment. This function is a macro4 function, which comes with limitations. It’s essential to include a named range in your spreadsheet because of the GET.CELL function in Excel only functions on one. We will use the GET.CELL function to get the color codes, and then using the COUNTIF function, we will count the particular colored cell.
⬇️⬇️ STEPS ⬇️⬇️
- Click Define Name under the Formulas tab in the Define Names This causes Excel to open the New Name dialog box, where you can enter information about your range.
- Give your range the name NumColor. Keep the Scope as it is. You can type the following formula into the Refers to Here,38 refers to the color code of the cells.
=GET.CELL(38,’Method 2′!C6)
- Now, in cell F6, type =Numcolor and select cell C6. As you can see, the code of the color in cell C6 is 53.
- Use Fill Handle to copy the formula for the entire range.
- Next to count a particular code, type this formula in cell C16.
=COUNTIF($F$6:$H$14,53)
- Finally, press Enter to see the number of times the selected color appeared in the range.
Just change the color code and use the same formula, for every color.
3. Creating VBA Code
We can achieve the same goal using a VBA code if you want. To write your code, you need to first access the Developer tab. Click here to see how to access the Developer tab. Let’s see how we can use the coolest version of Excel to count the blank colored cells. Basically, we will create a new function for this exact purpose.
⬇️⬇️ STEPS ⬇️⬇️
- While on the Developer tab, click the Visual Basic button in the Ribbon’s left-hand corner.
- Thus, a window will show up where you will click Insert and subsequently, Module.
- In the module, copy and paste the following formula.
Function CountBlankColoredCell(CountRange As Range, CountColor As Range)
Dim BColor As Integer
Dim SUM As Integer
BColor = CountColor.Interior.ColorIndex
Set Cell = CountRange
For Each Cell In CountRange
If Cell.Interior.ColorIndex = BColor Then
SUM = SUM + 1
End If
Next Cell
CountBlankColoredCell = SUM
End Function
- Now, get out of the module, and on cell C16, type this formula.
=CountBlankColoredColor($C$6:$E$14,C6)
- Finally, press Enter to see the count of the selected color in the C6.
Use the same formula for the rest of the colors.
📄 Important Notes
📌 The GET.CELL function is a Macro4 function, which is an old function, meaning it doesn’t work like the rest of the functions. You can’t use it in the formula bar.
📌 The function we created using the VBA code is a temporary one.
📝 Takeaways from This Article
🖊️ First, we demonstrated the Find & Select feature to count blank colored cells in Excel.
🖊️ Next, we used the GET.CELL function to get the color code and the COUNTIF function to count the codes.
🖊️ Finally, we created a new function using VBA code to achieve the particular task.
Conclusion
We have come to the conclusion of our article. I hope that after reading this article, you can easily count blank colored cells in Excel. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.
