WPS Spreadsheet: Count Blue, Green, Purple, and Blank Cells with COUNTIF
Question details
Count cells in a range that contain the text values blue, green, or purple, with an option to include blank cells.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user needs one formula to total several specified text values in D2:D12 instead of writing separate comparisons.
- Observed behavior
- The required result is a combined count of matching color-name text values and, when needed, empty cells.
Confirm that the cells contain color names as text, such as "blue," rather than only colored cell fills. COUNTIF evaluates cell values and does not count cells by their fill color.
Use SUM with COUNTIF and an inline criteria array
Use this formula when the color-name criteria are fixed and you want a single total for several text values.
COUNTIF can receive an array of criteria, returning one count for each item. SUM combines those counts into one result.
Click the empty cell where you want WPS Spreadsheet to display the combined count.
Enter =SUM(COUNTIF(D2:D12,{"blue","green","purple"})) and press Enter to count cells containing blue, green, or purple.
To include empty cells in the same total, replace the formula with =SUM(COUNTIF(D2:D12,{"blue","green","purple",""})) and press Enter.
Reference a criteria list instead of typing color names in the formula
Use this method when you want to change the color names without editing the formula.
Count Multiple Text Values Quickly with WPS Spreadsheet COUNTIF
WPS Spreadsheet lets you combine COUNTIF with SUM to count several text criteria in one formula, including an optional blank criterion. You can use inline criteria or maintain a separate criteria list for easier updates.
- 1. Open the worksheet in WPS Spreadsheet: Open the workbook, then select the worksheet that contains the values in D2:D12.
- 2. Choose an output cell: Click an unused cell where the total count should appear.
- 3. Enter the multiple-criteria formula: Type =SUM(COUNTIF(D2:D12,{"blue","green","purple"})) to count the three color-name text values.
- 4. Add the blank criterion if required: Use =SUM(COUNTIF(D2:D12,{"blue","green","purple",""})) when empty cells should be included in the total.
Frequently Asked Questions
Can COUNTIF count cells by their blue, green, or purple fill color?
No. COUNTIF counts cell values, not formatting. The formula works when the cells contain text such as "blue," "green," or "purple."
How do I count blue, green, purple, and blank cells in one formula?
In WPS Spreadsheet, use =SUM(COUNTIF(D2:D12,{"blue","green","purple",""})). The empty quoted criterion "" counts blank cells.
Why should I use SUM around COUNTIF?
The criteria array produces separate counts for each criterion. SUM adds those individual counts into one total.
Can I keep the color criteria in worksheet cells?
Yes. Put the criteria in cells such as F2:F4 and use =SUM(COUNTIF(D2:D12,F2:F4)). Add a blank cell to the criteria range if you also need to count blanks.
