How to Count Drop-Down Selections and Colored Cells in WPS Office Spreadsheets
Question details
The user needs to count how many cells in a results column contain each drop-down option, such as Pass, Merit, Distinction, and Fail, and how many cells have red or green fills.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet contains a drop-down list in a results column and red or green cell formatting, and the user needs summary counts for each selection and color.
- Observed behavior
- The user is looking for reliable formulas or methods to count drop-down selections and colored cells.
Before you start, identify the exact range that contains the drop-down selections, such as A2:A100. Also confirm whether the red or green fills are applied manually or through conditional formatting, because the counting method depends on this.
Count Drop-Down Selections with COUNTIF
Use this method when each result is stored as a text value in a drop-down list and you need totals for Pass, Merit, Distinction, or Fail.
COUNTIF counts cells that meet a single text condition, so it works directly in WPS Office Spreadsheets and Excel without enabling macros.
Click an empty cell where you want the count to appear, for example B2.
Type =COUNTIF(A2:A100,"Distinction") and press Enter. Replace A2:A100 with your actual drop-down range.
In another cell, enter =COUNTIF(A2:A100,"Merit"), then create similar formulas for Pass and Fail.
Count Red or Green Cells That Use Conditional Formatting
Use this method when red and green fills are created by conditional formatting; count based on the same rules rather than the visible color.
Count Manually Filled Red or Green Cells with a VBA Function
Use this method only when cells were colored manually and you can use a macro-enabled workbook in a desktop spreadsheet application.
Count Drop-Down Selections and Colored Cells in WPS Office Spreadsheets
WPS Office Spreadsheets can count Pass, Merit, Distinction, and Fail selections with COUNTIF and helps you count red or green cells by their conditional formatting rules. It opens and saves .xlsx files, so existing Excel workbooks remain fully compatible.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the .xlsx file that contains the drop-down list and colored cells.
- 2. Enter COUNTIF formulas: Click an empty cell and type =COUNTIF(A2:A100,"Distinction"), then repeat for Pass, Merit, and Fail.
- 3. Check conditional formatting rules: Select the colored cell range, go to Home > Conditional Formatting > Manage Rules, and note the red or green rule criteria.
- 4. Count colored cells by their rules: Use COUNTIF or COUNTIFS with the same criteria, for example =COUNTIF(A2:A100,"Fail") to count red Fail cells.
Frequently Asked Questions
What formula counts Pass, Merit, Distinction, or Fail in a drop-down column?
Use =COUNTIF(A2:A100,"Distinction") and change the text for Pass, Merit, or Fail. If your options are in another range, replace A2:A100 with your actual drop-down range.
Can WPS Spreadsheets count cells by their red or green fill color?
There is no standard worksheet function that reliably counts visible fill color. If the color comes from conditional formatting, count the cells using the same rule criteria. If the fill was applied manually, use VBA in a desktop macro-enabled workbook.
How do I count conditionally formatted red cells?
Open Home > Conditional Formatting > Manage Rules, note the rule that makes cells red, then use COUNTIF or COUNTIFS with that same condition, such as =COUNTIF(A2:A100,"Fail").
Does counting manual fill colors work in Excel for the web or mobile?
No, VBA color-counting functions work only in desktop spreadsheet applications such as Excel for Windows or Mac or WPS Office on Windows, not in web or mobile apps.
Can I count a drop-down selection and a color in one formula?
Yes, if the color is created by conditional formatting, use COUNTIFS with the same criteria. For example, =COUNTIFS(A2:A100,"Pass",B2:B100,">=80") counts Pass entries that also meet the rule threshold.
