Function Problems

Use COUNTIFS in Excel to Count Records by Gender and Shirt Size

Ayan MasoodAyan Masood Nov 14, 2026 868 views

Question details

Count records that match both a gender and a shirt size in an Excel table.

Count Excel Records by Gender and Shirt Size with COUNTIFS
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table contains separate gender and shirt-size columns, and the user wants counts for specific combinations.
Observed behavior
The desired result is a count of rows that meet both criteria, or a summary of all gender and size combinations.
Before you start

Identify the gender and shirt-size columns and check that each row represents one record. Make sure the values use consistent labels, such as “male” and “small,” without extra spaces.

Solution 1Recommended

Count a Gender and Shirt-Size Combination with COUNTIFS

Use COUNTIFS to count rows that meet both criteria in one formula.

1
Select a result cell

In Excel, click an empty cell where you want the count to appear.

2
Enter the formula

If gender is in A2:A100 and shirt size is in B2:B100, enter =COUNTIFS($A$2:$A$100,"male",$B$2:$B$100,"small") and press Enter.

3
Count other combinations

Replace "male" and "small" in the formula with the labels you want to count. Adjust both ranges to cover your data, keeping them the same size.

Count a Gender and Shirt-Size Combination with COUNTIFS
Matching criteria: COUNTIFS is not case-sensitive, but extra spaces or different labels can prevent a match. The dollar signs keep the ranges fixed if you copy the formula.
Count with WPS Spreadsheets

Count Gender and Shirt-Size Matches in WPS Spreadsheets

Use COUNTIFS in WPS Spreadsheets to count rows that match both a gender and a shirt size.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and choose Open to select your workbook.
  2. 2. Choose a result cell: Click an empty cell where you want to display the count.
  3. 3. Enter COUNTIFS: For gender in A2:A100 and size in B2:B100, enter =COUNTIFS($A$2:$A$100,"male",$B$2:$B$100,"small") and press Enter.
  4. 4. Adjust the criteria: Edit the ranges and replace the quoted labels to count the gender and shirt size in your data.
Count matching records using multiple criteriaOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why use COUNTIFS instead of COUNTIF for this count?

COUNTIFS evaluates multiple criteria together. For example, =COUNTIFS(A2:A100,"male",B2:B100,"small") counts only rows matching both labels.

Can I put the criteria in cells instead of the formula?

Yes. If the gender is in D2 and the size is in E2, use =COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,E2). Change D2 and E2 to count another combination.

Why does COUNTIFS return zero when I can see matching records?

Check that the criteria match the labels in the gender and size columns and that the ranges cover the relevant rows. Extra spaces in the data can prevent a match.

How can I see counts for every gender and size combination?

Select the data and choose Insert > PivotTable. Put Gender in Rows, Shirt Size in Columns, and a nonblank record field in Values, then set the value calculation to Count.