COUNTIF Fill Color in Excel (Easiest Way in 2025)
In this article, we will show you how to use the COUNTIF function to count cells with fill color in Excel. Simply follow the steps below.
COUNTIF Function to Count Cells with Fill Color in Excel
Follow the steps below to count cells with fill color in Excel by using the COUNTIF function. This method involves using Excel’s old Macro4 language, which can still be accessed through defined names. We will use a simple dataset that lists sales figures for various products over several days. Cells with sales over 250 are filled with yellow to indicate high sales volumes.
1. Open Name Manager
Go to the Formulas tab on the Ribbon. Click on Name Manager then click New to create a new defined name.
2. Set Up the Defined Name
In the Name field, enter a name like “ColorIndex”. In the Refers to field, enter the following Macro4 function: =GET.CELL(38, INDIRECT(“rc”, FALSE)). This formula uses GET.CELL(38, …) to get the fill color index of a cell. INDIRECT(“rc”, FALSE) refers to the cell in which this formula is used, making it dynamic. Click OK to save the defined name and close the Name Manager.
3. Insert a Helper Column
Insert a new column next to your data where you will apply the defined name. For simplicity, let us say this is Column G.
4. Apply the Defined Name
In cell G2 (next to the first data row), enter this formula: =ColorIndex. Drag this formula down to apply it to the rest of the cells in Column G. This column will now display the color index numbers for each corresponding cell in Column F.
5. Find the Color Index for Yellow
Look through Column G to identify the index number that corresponds to yellow. Since all the cells in Column F have yellow fill color, all the cells in Column G show “6” (Note: This index can vary depending on the Excel version and theme).
6. Use COUNTIF to Count Yellow Cells
In a new cell, use the COUNTIF function to count how many cells in Column G have the index “6”. Let us select cell E7. Enter this formula: =COUNTIF(G2:G4, 6).The formula will output the number of cells with a yellow background in your specified range.
We hope that you now have a better understanding of how to count cells with fill color in Excel using COUNTIF. If you enjoyed this article, you might also like our article on how to autofill weekly dates in excel and our article on excel fill color based on value.
