WPS Spreadsheet: Count Codes for One Person Across a Variable Data Range
Question details
The user needs to count matching codes across several columns for a specific person in a variable data range.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Names are listed in one column, while each person’s codes are distributed across multiple columns. The user wants to count codes containing a pattern such as "bn" for one selected person.
- Observed behavior
- COUNTIFS cannot directly combine the one-column name range A8:A27 with the two-dimensional range B8:AF27 because the criteria ranges do not have compatible dimensions.
Confirm that the names are in A8:A27 and the corresponding code data is in B8:AF27. Check the exact spelling of the target name and the code pattern, such as "*bn*", before entering the formula.
Find the person’s row before counting matching codes
Use this formula when one name column corresponds to multiple code columns and COUNTIFS reports incompatible range dimensions.
The formula first locates the matching name, returns that person’s entire row from the data range, and then counts cells containing the requested code pattern. This keeps the name lookup and code count dimensionally aligned.
In WPS Spreadsheet, click the cell where you want the total to appear.
Enter =LET(r,XMATCH("jane doe",A8:A27),COUNTIF(INDEX(B8:AF27,r,0),"*bn*")) and press Enter.
Replace "jane doe" with the required name and change "*bn*" if you need a different code pattern. The asterisks make the match find bn anywhere in a cell.
Count Person-Specific Codes Easily in WPS Spreadsheet
WPS Spreadsheet lets you identify the correct person’s row and count matching codes across multiple columns with a focused formula, avoiding incompatible COUNTIFS dimensions while keeping the workflow familiar for Excel users.
- 1. Open the workbook: Launch WPS Office, open WPS Spreadsheet, and select the workbook containing the names and code data.
- 2. Check the source ranges: Verify that the names are in A8:A27 and the related code grid is in B8:AF27.
- 3. Enter the formula: Click an empty result cell and enter =LET(r,XMATCH("jane doe",A8:A27),COUNTIF(INDEX(B8:AF27,r,0),"*bn*")) to count matching codes for the selected person.
- 4. Customize and verify: Replace the name or wildcard criteria as needed, then compare the result with the matching person’s row to confirm the count.
Frequently Asked Questions
Why does COUNTIFS fail with A8:A27 and B8:AF27?
A8:A27 is a single-column range, while B8:AF27 is a two-dimensional range. COUNTIFS generally expects criteria ranges with compatible dimensions, so locate the person’s row first and then count within that row.
What formula counts codes containing bn for Jane Doe?
Use =LET(r,XMATCH("jane doe",A8:A27),COUNTIF(INDEX(B8:AF27,r,0),"*bn*")). The XMATCH function finds Jane Doe’s position, INDEX returns the corresponding row, and COUNTIF counts cells containing bn.
Can I use this formula if the name is stored in another cell?
Yes. If the target name is in H2, replace "jane doe" with H2: =LET(r,XMATCH(H2,A8:A27),COUNTIF(INDEX(B8:AF27,r,0),"*bn*")).
What should I use if LET or XMATCH is unavailable?
Use =COUNTIF(INDEX(B8:AF27,MATCH("jane doe",A8:A27,0),0),"*bn*"). MATCH finds the name’s position and INDEX supplies the corresponding data row.
What do the asterisks in "*bn*" mean?
The asterisks are wildcards. "*bn*" counts cells where bn appears anywhere in the text. Use "bn*" for codes beginning with bn or "*bn" for codes ending with bn.
