How to Count Columns Containing an X in Each Excel Row in WPS Office Spreadsheets
Question details
The user needs a formula to count how many cells in each row contain the value X, such as X entries across columns B through F in a worksheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has row values across columns B through F, and the user wants to return a row-by-row count in column G.
- Observed behavior
- The goal state is to produce an accurate count for each row, with X counted regardless of uppercase or lowercase.
Open your workbook in WPS Office Spreadsheets and confirm that the values to count are in a consistent range such as B2:F2. Make sure the column where you will place the results is empty so the formula can be copied down cleanly.
Use COUNTIF to Count Cells Containing X in a Row
Use this method when you need a simple row-by-row count of cells that contain X, such as entries in B2:F2.
COUNTIF is the simplest way to get a case-insensitive count of x or X in a row. It checks each cell in the specified range against the criteria and returns the number of matching cells.
Click cell G2, where you want the first row's count to appear.
Type =COUNTIF(B2:F2,"x") and press Enter. WPS Office Spreadsheets counts cells containing x regardless of letter case.
Drag the fill handle from G2 down through the remaining rows to apply the same count to each row.
Count Columns Containing at Least One X Across Multiple Rows with SUMPRODUCT
Use this formula when you need the number of columns that contain at least one X across multiple rows, rather than individual row counts.
Count X Values in Rows with WPS Office Spreadsheets
WPS Office Spreadsheets can complete this count directly with the COUNTIF function. Open your Excel-compatible workbook, enter the formula in the result column, and copy it down to count x/X values row by row.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the Excel file that contains the rows you want to count.
- 2. Select the first result cell: Click G2, or the first empty cell in the column where row counts should appear.
- 3. Enter the COUNTIF formula: Type =COUNTIF(B2:F2,"x") and press Enter to count x/X in that row.
- 4. Copy the formula down: Use the fill handle to drag the formula from G2 through the last row of data.
Frequently Asked Questions
Does =COUNTIF(B2:F2,"x") count both uppercase and lowercase X?
Yes. COUNTIF criteria are not case-sensitive in WPS Office Spreadsheets and Excel, so the formula counts both x and X.
How do I count cells that contain X anywhere in the cell instead of cells that equal X?
Use a wildcard criteria such as =COUNTIF(B2:F2,"*x*") to count cells where x appears anywhere in the cell value.
Can I copy this formula down for many rows at once?
Yes. Enter the formula in the first result cell, select it, and double-click or drag the fill handle down through the last row to apply it.
How do I count columns with at least one X across multiple rows?
Use a SUMPRODUCT and COUNTIF formula such as =SUMPRODUCT(N((COUNTIF(OFFSET(B2:F2,ROW(1:5)-1,),"x")>0))). Adjust B2:F2 and ROW(1:5) to the actual range, and press Ctrl+Shift+Enter if you are using legacy Excel.
