How to Count Excel Rows Where Team Members Total a Specified Value
Question details
The user needs to count table rows where the total number of team members across several team columns equals a specified value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a worksheet table with separate Team 1, Team 2, and Team 3 columns and a second table listing requested team member totals. They need to count how many rows in the first table match each listed total.
- Observed behavior
- The user needs to first compute each row's team member total and then count the rows whose total equals the requested value.
Confirm that the workbook contains an Excel table such as Table1 and that the Team 1, Team 2, and Team 3 columns contain numeric values. If the second table uses a structured reference such as Number of Team Members Total Per Case, verify that the column name exactly matches.
Count Rows by Adding a Total Helper Column and Using COUNTIF
Use this when you can add a column to the first table and want a clear, auditable count that updates automatically.
A helper Total column stores the row-by-row sum of Team 1, Team 2, and Team 3. The COUNTIF formula then counts every row in the Total column that equals the requested team member total.
Click the first empty column header in Table1, type Total, and press Enter to create the helper column.
In the first data row of the Total column, type =SUM(Table1[@[Team 1]:[Team 3]]) and press Enter. If WPS Spreadsheets does not fill the column automatically, drag the fill handle down through the table rows.
Click the cell in the second table where the count should appear, type =COUNTIF(Table1[Total],[@[Number of Team Members Total Per Case]]), and press Enter.
Count Rows Without a Helper Column Using SUMPRODUCT
Use this if you cannot add a Total column and want a single formula to count matching rows.
Count Excel Rows by Team Member Totals in WPS Spreadsheets
Open your Excel workbook in WPS Spreadsheets and use a helper Total column plus COUNTIF to count rows whose Team 1–Team 3 total equals a specified value. WPS Spreadsheets supports Excel table formulas, structured references, and automatic calculated columns while preserving .xlsx format compatibility.
- 1. Install WPS Office: Download WPS Office from the official site, install it, and open your .xlsx workbook in WPS Spreadsheets.
- 2. Add the Total helper column: In Table1, click the first blank column header, type Total, and press Enter.
- 3. Calculate row totals: In the first row of the Total column, enter =SUM(Table1[@[Team 1]:[Team 3]]) and press Enter. WPS Spreadsheets applies the formula to the table column.
- 4. Count matching cases: In the second table, select the count cell and enter =COUNTIF(Table1[Total],[@[Number of Team Members Total Per Case]]), then press Enter.
- 5. Verify and expand: Change a team value or add a table row and confirm that the count updates automatically.
Frequently Asked Questions
What should I check if the COUNTIF result is 0?
Confirm that the Total column contains numeric values and that the second table's criteria column or cell exactly equals the total you are counting. Text-formatted numbers, extra spaces, or mismatched column names can cause COUNTIF to return 0.
Can WPS Spreadsheets use Excel structured references such as Table1[@[Team 1]:[Team 3]]?
Yes. WPS Spreadsheets supports Excel table structured references, so the same formulas work when you open an .xlsx workbook.
How do I make the formulas apply to newly added rows automatically?
Keep the data in an Excel table. WPS Spreadsheets fills calculated columns automatically, including the Total column and any formula placed in a table column.
Can I count the rows without adding a Total column?
Yes, use =SUMPRODUCT(--(Table1[Team 1]+Table1[Team 2]+Table1[Team 3]=[@[Number of Team Members Total Per Case]])) in the second table.
What if my second table does not have the column 'Number of Team Members Total Per Case'?
Replace [@[Number of Team Members Total Per Case]] with the cell reference that contains the requested total, such as =COUNTIF(Table1[Total],F2).
