How to Count Attendees by Category in WPS Spreadsheets
Question details
The user needs to count meeting attendees by category from attendance values in column H and category names in column I using COUNTIFS or a PivotTable.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an attendance sheet with attendance values in column H and category names in column I, and wants a reliable category-wise count.
- Observed behavior
- The intended result is a category-wise summary of attendees and non-attendees based on attendance values such as 1 and 0.
Confirm that column H contains numeric attendance values such as 1 and 0, and that every row in column I has a matching category label. This prevents formula mismatches and PivotTable grouping issues.
Use COUNTIFS to Count Attendees by Category
Use this method when you need a quick formula result in a summary cell and want to set the attendance criteria directly.
In a summary cell, such as L2, type the category name exactly as it appears in column I.
In the next cell, type =COUNTIFS($I$2:$I$100,$L2,$H$2:$H$100,1) and press Enter. Change 1 to 0 to count non-attendees.
Select the formula cell and drag the fill handle down to apply it to other category names listed in column L.
Create a PivotTable to Summarize Attendees and Non-Attendees by Category
Use this method for a dynamic, less error-prone summary that updates as you change the source attendance data.
Count Attendees by Category in WPS Spreadsheets
Use native WPS Spreadsheets tools such as COUNTIFS and PivotTables to count meeting attendees and non-attendees by category. WPS Office is free, lightweight, and reads and writes Microsoft Excel .xlsx files, so you can open existing attendance workbooks without changing your workflow.
- 1. Download and install WPS Office: Go to the official WPS website and click Download WPS Office Free, then install WPS Spreadsheets on your device.
- 2. Open the attendance workbook: Launch WPS Spreadsheets and open the .xlsx workbook that contains attendance values in column H and category names in column I.
- 3. Use COUNTIFS for a quick count: In a summary cell, enter =COUNTIFS($I$2:$I$100,$L2,$H$2:$H$100,1) and press Enter to count attendees for the category in L2.
- 4. Create a PivotTable for a dynamic summary: Select the data range, go to Insert > PivotTable, and drag Category to Rows and Attendance to Columns to display attendee and non-attendee counts by category.

Frequently Asked Questions
Can COUNTIFS count both attendees and non-attendees in WPS Spreadsheets?
Yes. Use =COUNTIFS($I$2:$I$100,$L2,$H$2:$H$100,1) for attendees and replace 1 with 0 for non-attendees. Put the two formulas in separate summary cells.
Why does my COUNTIFS formula return 0 even though matching categories exist?
This usually happens when the category name in the summary cell does not exactly match column I or when attendance values are stored as text. Check for extra spaces and use the same data type, or wrap the value in quotation marks.
How do I make the counts update when I add new attendance rows?
With a PivotTable, select the PivotTable and click Refresh on the PivotTable Analyze tab. With COUNTIFS, use a range larger than the current data or format the source as a table and use structured references.
Can I open the same attendance workbook in Microsoft Excel after using WPS Spreadsheets?
Yes. WPS Spreadsheets saves to .xlsx format, so your COUNTIFS formulas and PivotTables remain compatible with Microsoft Excel.





