Pivot Table Issues

How to Count Shirts by Size and Gender in Excel Pivot Tables

Adam Davis Oct 8, 2026 871 views

Question details

The user needs to summarize employee data to get a total count of shirts categorized by both size and gender using a Pivot Table.

How to Count Shirts by Size and Gender in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing and tallying employee clothing sizes based on gender for inventory or ordering purposes.
Observed behavior: A summarized cross-tabulation of shirt counts is needed, grouped by size and gender, potentially filtered by work shift.

Before you start

Ensure your employee data is formatted as a continuous table with clear column headers (e.g., Employee Name, Size, Gender, Shift) and contains no blank rows or merged cells.

Solution 1Recommended

Create a Cross-Tabulated Pivot Table

Use a two-way matrix by placing Size in rows and Gender in columns to get a clear grid of counts.

This method creates a clean matrix view, which is the most common way to display two intersecting categories like Size and Gender.

Insert the Pivot Table

Select any cell inside your data range. Go to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a New Worksheet and click 'OK'.

Set up Rows and Columns

In the PivotTable Fields pane on the right, drag the 'Size' field down into the 'Rows' area. Next, drag the 'Gender' field into the 'Columns' area.

Count the Items

Drag a unique text field, such as 'Employee Name' or 'Size', into the 'Values' area. Excel will automatically set the summarization to 'Count'.

Add a Filter (Optional)

If you need to view shirt counts by a specific group, drag the 'Shift' field into the 'Filters' area. You can now use the drop-down above the Pivot Table to select specific shifts.

Double-Check Value Settings: If your Pivot Table displays 'Sum' instead of 'Count', right-click the values in the table, select 'Summarize Values By', and choose 'Count'.

Solution 2

Create a Grouped Row Pivot Table

Stack Size and Gender together in the Rows area to view the counts in a single hierarchical list.

Free Spreadsheet Tool

Easily Summarize Data with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive Pivot Table features that function identically to Excel. You can quickly summarize, filter, and count your inventory or employee data entirely for free.

1. Open Your Data: Launch WPS Spreadsheet and open your employee or inventory data file.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top menu and select 'PivotTable'.
3. Select Data Range: Confirm the selected data range and choose whether to place the PivotTable in an existing or new worksheet.
4. Arrange Your Fields: In the side panel, drag the 'Size' field into Rows and the 'Gender' field into Columns.
5. Generate the Count: Drag 'Employee Name' into the Values area to instantly generate a cross-tabulated count of your shirts.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats without altering layouts.Advanced Pivot Table functionality to easily count sizes, genders, and other categories.Free and lightweight with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Pivot Table showing 'Sum' instead of 'Count'?

If the field you placed in the Values area contains numbers instead of text, Excel assumes you want to add them up and defaults to 'Sum'. To fix this, click the field in the Values area, select 'Value Field Settings', and change the summary type to 'Count'.

How do I filter the shirt counts by work shift or department?

Drag the 'Shift' or 'Department' field into the 'Filters' area in the PivotTable Fields pane. This will add a drop-down menu at the very top of your Pivot Table, allowing you to select and filter by specific shifts.

Do I need to do anything if I add a new employee to the original data?

Yes. Pivot Tables do not update automatically. After adding new data to your source table, right-click anywhere inside the Pivot Table and select 'Refresh' to update the shirt counts.

Can I sort the shirt sizes logically (Small, Medium, Large) instead of alphabetically?

Yes. You can manually reorder rows by clicking the edge of the cell containing the size (like 'Small') and dragging it up or down to your preferred position. Alternatively, you can create a custom sorting list in Excel's advanced options.

Excel & Data Problems