Function Problems

How to Count Nonblank Cells That Are Not Red in Excel

Huma Ashraf Ch Oct 10, 2026 872 views

Question details

The user needs to count cells in a spreadsheet that contain data (nonblank) but do not have a red background fill color.

How to Count Nonblank Cells That Are Not Red in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking document statuses or analyzing data where both content existence (nonblank) and a specific cell formatting (not red) indicate a qualifying record.
Observed behavior: Standard Excel functions like COUNTIF cannot natively evaluate or count cells based on their background color, making direct formula calculation impossible without workarounds.

Before you start

Before attempting to count cells by color, check if the red fill was applied manually or triggered by Conditional Formatting, as this determines which method or VBA script will work best.

Solution 1Recommended

Use an Auxiliary Column with COUNTIFS

The most reliable approach is to base the calculation on cell values rather than cell color by introducing a dedicated status column.

Because standard Excel formulas cannot read cell formatting, counting colors directly is prone to errors. A better long-term strategy is to store the condition that causes the red color (e.g., 'Overdue' or 'Rejected') in a separate column and count that text value.

Add a status column

Insert a new column next to your data (for example, Column B). Enter the text conditions that represent the status of each row, instead of just coloring the cells red.

Apply the COUNTIFS formula

Click on an empty cell where you want the result. Enter the formula =COUNTIFS(A:A, "<>", B:B, "<>Overdue") to count cells in Column A that are not blank, and where Column B does not contain the condition representing the red color.

Press Enter to calculate

Hit Enter on your keyboard. The formula will automatically update the count if the data values change, ensuring perfect reliability.

Best Practice: Basing calculations on actual cell data rather than formatting ensures your workbook remains stable, calculates instantly, and avoids complex VBA programming.

Solution 2

Use a Custom VBA Function (For Manual Colors)

If cell color is the absolute only indicator and you cannot add helper columns, you can create a custom VBA function to count the nonblank, non-red cells.

Easily Analyze and Manage Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools and full compatibility with complex logic formulas, allowing you to seamlessly manage data conditions without relying on fragile color-counting workarounds.

1. Open your workbook in WPS: Launch WPS Office and open your .xlsx or .xls spreadsheet file.
2. Set up a criteria column: Create a new column to define the status of your data logically, replacing the need for color-based calculations.
3. Use the COUNTIFS function: Type =COUNTIFS() referencing your data ranges to quickly and reliably count the nonblank cells that meet your conditions.

Fully compatible with Microsoft Excel formulas like COUNTIF and COUNTIFSLightweight, fast, and free alternative for complex data analysisBuilt-in advanced filtering by color to quickly isolate and view dataRobust macro support for users who prefer custom VBA scripting

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard Excel formulas to count cells by their background color?

No, standard Excel formulas (like COUNTIF or COUNTIFS) cannot read or evaluate cell formatting, including fill colors. You must use VBA, or filter the data by color and subtotal it.

Why doesn't my VBA script count colors applied by Conditional Formatting?

Standard VBA 'Interior.Color' properties only read manually applied colors. To read colors applied by Conditional Formatting, you need a more advanced VBA script that utilizes the 'DisplayFormat.Interior.Color' property.

How can I count nonblank, non-red cells without using formulas or VBA?

You can use the Filter tool. Select your data headers, click 'Filter', use 'Filter by Color' to exclude red cells, and uncheck 'Blanks'. Then, highlight the remaining cells to see the count in the bottom status bar, or use the =SUBTOTAL(103, A:A) formula.

Excel & Data Problems