How to Highlight Rows and Count Values with Conditional Formatting and COUNTIF in WPS Spreadsheets
Question details
The user wants to format an entire row in a spreadsheet when the value in column C reaches 25, and count matching values with COUNTIF.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing a worksheet where rows should be visually flagged when a value in column C reaches 25, and where matching values need to be counted.
- Observed behavior
- The user needs guidance on creating a formula-based conditional formatting rule for entire rows and on using COUNTIF to count values equal to 25.
Open your workbook in WPS Spreadsheets and confirm which column contains the threshold values, for example column C. If you want the formula =$C2>=25 to start at row 2, make sure row 2 is the active cell in the selected range.
Highlight Entire Rows with Formula-Based Conditional Formatting When Column C Reaches 25
Use this method when you want WPS Spreadsheets to apply dark red bold strikethrough formatting across an entire row whenever the column C value is 25 or greater.
Conditional formatting can set font color, bold, and strikethrough, but it cannot change font family or font size.
Select the range you want to format, for example A2:D26, with the active cell in row 2.
On the Home tab, click Conditional Formatting, then select New Rule.
Select the option "Use a formula to determine which cells to format".
In the formula field, enter =$C2>=25.
Click Format, open the Font tab, select Strikethrough and Bold, and choose dark red as the font color. Confirm the rule to apply it.
Count Values Equal to 25 with COUNTIF in WPS Spreadsheets
Use this method when you need a total count of cells in a specific column that exactly equal 25.
Use WPS Spreadsheets to Highlight Rows and Count Values with Conditional Formatting and COUNTIF
You can complete this task directly in WPS Spreadsheets by creating a formula-based conditional formatting rule to highlight entire rows when column C reaches 25, and by using COUNTIF to total matching values.
- 1. Open your workbook: Launch WPS Spreadsheets and open the workbook containing the data.
- 2. Select the data range: Select the range you want to format, with the active cell in row 2, such as A2:D26.
- 3. Create the conditional formatting rule: Go to Home > Conditional Formatting > New Rule, choose a formula-based rule, and enter =$C2>=25.
- 4. Set the desired formatting: Click Format, open the Font tab, and apply dark red font color, Bold, and Strikethrough.
- 5. Count the matching values: Click a result cell and enter =COUNTIF(C2:C26,25), then press Enter.
Frequently Asked Questions
Can conditional formatting change font family or font size in WPS Spreadsheets?
No, conditional formatting can set font color, bold, and strikethrough, but it cannot change font family or font size. Change those in regular cell formatting instead.
How do I apply the rule to an entire row instead of only column C?
Select the entire range you want to format, such as A2:D26, and use the formula =$C2>=25. The $ before C locks the column, while the row number increments for each row.
What COUNTIF formula counts only cells equal to 25?
Use =COUNTIF(C2:C26,25). This returns the number of cells in C2:C26 that exactly equal 25.
Can I use a cell reference instead of typing 25 in COUNTIF?
Yes. Put the threshold value in a cell, such as F1, and use =COUNTIF(C2:C26,F1). The formula will count cells equal to the value in F1.
What should I do if my data starts in a different row?
Adjust the row reference in the conditional formatting formula to match the active row. For example, if the active row is 5, use =$C5>=25.
