Count Excel Records by Age and Gender with COUNTIFS
Question details
Count spreadsheet records that match both an age condition and a gender condition by using the COUNTIFS function.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a summary table that counts M or F records for each selected age or age range.
- Observed behavior
- The desired result is a count that includes only rows meeting every specified age and gender criterion.
Confirm that the age column contains real numeric values and that the gender column uses consistent entries such as M and F. Set up the age criteria and gender headings in separate cells before entering the formula.
Count records for one exact age and one gender
Use this formula when each summary row represents one exact age and each summary column represents a gender value.
COUNTIFS evaluates all criteria ranges row by row and returns the number of records that match every condition. Absolute references keep the source columns fixed when you copy the formula.
Use the age range $O$96:$O$999999 and the gender range $M$96:$M$999999, or replace these references with the matching columns in your worksheet.
In the first result cell, enter =COUNTIFS($O$96:$O$999999,E$1,$M$96:$M$999999,$F1). Here, E$1 contains the age and $F1 contains the gender criterion.
Drag the fill handle across for other gender headings and down for other ages. The mixed references E$1 and $F1 adjust to the appropriate age and gender cells.
Count records within an age range and gender
Use this method when the summary should include an inclusive age band, such as ages 18 through 25, plus a selected gender.
Count age and gender records with COUNTIFS in WPS Spreadsheets
WPS Spreadsheets supports the COUNTIFS function for multi-condition summaries. Enter the same exact-age or age-range criteria to count records by age and gender in a familiar spreadsheet interface.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office and select the worksheet containing the age and gender data.
- 2. Create summary criteria cells: Enter the target age or age limits in summary cells, then enter M or F in a nearby gender heading or criteria cell.
- 3. Enter the COUNTIFS formula: Select the first result cell and type =COUNTIFS($O$96:$O$999999,E$1,$M$96:$M$999999,$F1) for an exact age, adjusting the ranges as needed.
- 4. Fill the summary table: Use the fill handle in the lower-right corner of the result cell to copy the formula across gender columns and down age rows.
Frequently Asked Questions
How do I count females who are exactly 30 years old in Excel?
Use COUNTIFS with one age criterion and one gender criterion, for example: =COUNTIFS($O$96:$O$999999,30,$M$96:$M$999999,"F"). Replace the ranges with your age and gender columns.
Can COUNTIFS count an age range instead of one age?
Yes. Repeat the age range twice in COUNTIFS: one criterion for the lower limit and one for the upper limit, such as ">="&E1 and "<="&E2.
Why does my COUNTIFS formula return zero for a visible age and gender?
Check whether ages are stored as text instead of numbers and whether gender values contain extra spaces or inconsistent labels, such as Female instead of F. Use matching criteria that reflect the actual cell values.
How can I copy a COUNTIFS formula without changing the source data ranges?
Use absolute references for the source ranges, such as $O$96:$O$999999 and $M$96:$M$999999. Use mixed references like E$1 and $F1 for criteria that should change only across or down.
