Can Microsoft Excel COUNTIF Count Cells by Background Color?
Question details
Determine whether COUNTIF can count red or blue cells and choose a reliable way to count records represented by background colors.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Count cells with specific background colors, potentially alongside text criteria such as Yes in one column and X in another.
- Observed behavior
- Standard COUNTIF and COUNTIFS formulas evaluate cell values, not manually applied background colors. Conditional formatting colors must be counted through their underlying conditions or handled separately.
Select a colored cell and check Home > Conditional Formatting > Manage Rules to determine whether its color comes from a rule or a manual fill. Identify the range to count and what red and blue represent before choosing a method.
Record color categories in a helper column and count them with COUNTIF
Use this approach when colors represent categories or statuses that can be stored as cell values.
COUNTIF cannot read a cell's fill color. A helper column makes the meaning of the color explicit, so formulas can count categories without depending on formatting.
Enter Category in C1 beside your data. In C2:C100, enter Red or Blue for each corresponding record, or use meaningful labels such as Pending and Complete. These labels must be entered or derived from data; COUNTIF will not extract them from existing fills.
Select an empty result cell and enter =COUNTIF(C2:C100,"Red"). To count blue records, enter =COUNTIF(C2:C100,"Blue"). Adjust the ranges to match your worksheet.
If column A contains Yes and column B contains X, enter =COUNTIFS(A2:A100,"Yes",B2:B100,"X",C2:C100,"Red") in a result cell. All criteria ranges must cover corresponding rows.
Count the underlying condition for conditional formatting colors
Use this method when a rule automatically applies the red or blue background.
Use a VBA function to count manually applied fill colors in Excel
Use desktop Excel VBA when you need to count actual manual fills rather than category values.
Count color-coded categories with WPS Spreadsheet
In WPS Spreadsheet, store the meaning of each color in a helper column and use COUNTIF or COUNTIFS to count matching records. This approach avoids relying on manual fill detection or Excel VBA.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Add category labels: Enter Category in an unused column header, then enter Red or Blue beside each relevant row according to the meaning of its color.
- 3. Enter a count formula: Click an empty result cell and enter =COUNTIF(C2:C100,"Red"), replacing C2:C100 with your category range. Use "Blue" to count the other category.
- 4. Count matching records and save: For additional criteria, enter =COUNTIFS(A2:A100,"Yes",B2:B100,"X",C2:C100,"Red"). Choose Save As and use .xlsx to retain the helper values and formulas in an Excel-compatible workbook.
Frequently Asked Questions
Can I put a background color directly into a COUNTIF criterion?
No. COUNTIF and COUNTIFS evaluate values, not fill formatting. Enter a category such as Red in a helper column and use =COUNTIF(C2:C100,"Red"), or use a desktop Excel VBA function for manual fills.
How do I count red cells created by conditional formatting?
Open Home > Conditional Formatting > Manage Rules and inspect the condition. If red means a value is greater than 100, use =COUNTIF(B2:B100,">100"). If rules overlap, check priority and Stop If True before treating the result as a visible-color count.
Why does my VBA color count stay the same after I change a fill?
Changing formatting alone may not trigger recalculation. In desktop Excel, choose Formulas > Calculate Now or press F9. Also confirm that your sample cell matches the manual fill and that the target range is correct.
How can I count rows with Yes, X, and a red category?
Store Yes in column A, X in column B, and the color category in column C. Then enter =COUNTIFS(A2:A100,"Yes",B2:B100,"X",C2:C100,"Red"). This counts the Red label, not the cell's actual fill.
Will the VBA function work in Excel for the web?
Excel for the web cannot run VBA functions. Use a helper column with COUNTIF or COUNTIFS in the browser, or open the .xlsm workbook in desktop Excel to run the VBA function.
