How to Count Attendance by Gender or Category Across Multiple Cycles in Excel
Question details
The user needs to total attendance by gender or category across multiple cycles such as C1 and C2 in Excel, using COUNTIFS or SUMIFS depending on the worksheet layout.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has an attendance sheet with gender or category columns and multiple cycle columns, and wants totals for each group.
- Observed behavior
- Attendance totals across cycles can be calculated with COUNTIFS or SUMIFS, but the exact formula depends on how attendance is recorded and how categories are defined.
Confirm how attendance is recorded in your worksheet, such as using 1 for present and blank or 0 for absent, and identify the exact columns used for gender/category and each cycle, such as C1 and C2.
Count attendance with COUNTIFS when present is recorded as 1
Use this when each cycle column contains 1 for present and blank or 0 for absent, and you need a total for one gender or category.
COUNTIFS evaluates multiple criteria in the same row. If your attendance values are text or point values instead of 1, adjust the criteria or use SUMPRODUCT.
Click an empty cell where you want the attendance total to appear, such as H2.
Type =COUNTIFS(GenderRange,"Male",C1Range,1) and replace GenderRange and C1Range with your actual ranges, for example =COUNTIFS($B$2:$B$100,"Male",$C$2:$C$100,1).
Press Enter to get the total, then repeat for C2 by changing C1Range to C2Range, or add another COUNTIFS with a plus sign to combine cycles.
Count by any category across multiple cycles with SUMPRODUCT
Use this when you want one formula that counts rows matching a category across C1 and C2 at the same time, or when attendance is not simply 1 or 0.
Create attendance summaries by gender or category in WPS Office Excel
WPS Office Excel supports the same COUNTIFS and SUMIFS functions, so you can count attendance by gender or category across C1, C2, and other cycles with familiar formulas and .xlsx compatibility.
- 1. Open the attendance workbook: Launch WPS Office Excel and open the workbook that contains attendance data.
- 2. Select the result cell: Click the empty cell where you want the gender or category total to appear.
- 3. Enter the counting formula: Type =COUNTIFS(GenderRange,"Male",C1Range,1) with your actual range references, or use SUMPRODUCT for multiple cycles.
- 4. Press Enter and verify the total: Press Enter to calculate, then adjust ranges or add formulas for other cycles such as C2.
Frequently Asked Questions
What formula counts attendance by gender when 1 means present?
Use =COUNTIFS(GenderRange,"Male",C1Range,1). Replace GenderRange with the gender column and C1Range with the cycle column, then press Enter to get the count.
How do I count by category instead of gender?
Put the category column as the first criteria range and the category name as the criteria, for example =COUNTIFS(CategoryRange,"Youth",C1Range,1).
How can I combine C1 and C2 attendance in one formula?
Add two COUNTIFS results together or use =SUMPRODUCT((GenderRange="Male")*((C1Range=1)+(C2Range=1))).
Do I need dollar signs in the ranges?
Use absolute references such as $B$2:$B$100 when copying the formula to other cells so the ranges do not shift.
Does WPS Office support COUNTIFS and SUMIFS?
Yes. WPS Office Excel supports COUNTIFS and SUMIFS with the same syntax, so formulas from Microsoft Excel work without conversion.
