How to Use COUNTIFS to Count Data by First and Last Name in Excel
Question details
The user needs to accurately count records associated with specific staff members by matching both their first and last names using a formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking staff records where multiple employees might share the same last name, requiring a dual-criteria match to ensure correct counts.
- Observed behavior
- Standard PivotTables may omit staff members who have no matching records. The user requires a formula-based method to display counts for everyone on the roster, including those with zero records.
Ensure your dataset has separate columns for first names and last names without leading or trailing spaces, as extra spaces can cause the COUNTIFS function to incorrectly return zero.
Use COUNTIFS with Standard Cell Ranges
The standard COUNTIFS formula evaluates multiple criteria ranges simultaneously to ensure both first and last names match exactly before counting.
By utilizing absolute references (using the dollar sign $) for the search ranges, you can safely copy the formula down your roster without the reference shifting.
Click the cell next to the first staff member's name on your roster where you want the total count to appear.
Type =COUNTIFS(Tests!$A$2:$A$100, A2, Tests!$B$2:$B$100, B2). In this example, 'Tests!$A$2:$A$100' is the First Name range in your source sheet, 'A2' is the first name on your roster, 'Tests!$B$2:$B$100' is the Last Name range, and 'B2' is the last name on your roster.
Press Enter to get the result. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the staff members.
Use COUNTIFS with Structured References (Excel Tables)
If your source data is formatted as an Excel Table, using structured references makes the formula easier to read and automatically updates when new rows are added.
Easily Manage and Count Data with WPS Spreadsheet
WPS Spreadsheet is a powerful, free alternative to Microsoft Excel that fully supports complex functions like COUNTIFS. You can seamlessly count, analyze, and manage datasets with matching criteria without worrying about formula compatibility.
- 1. Download and install: Download WPS Office for free from the official website and install it on your device.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx) containing the staff roster.
- 3. Apply COUNTIFS: Use the formula builder or type =COUNTIFS() directly into the cell to analyze your multi-criteria data exactly as you would in Excel.
Frequently Asked Questions
Why does my COUNTIFS formula return 0 even when names visually match?
This is usually caused by hidden spaces, such as leading or trailing spaces, in either your source data or your criteria cells. You can use the TRIM() function to clean your text data before running the COUNTIFS formula.
Can I use wildcards with COUNTIFS for partial name matches?
Yes, COUNTIFS supports wildcards like the asterisk (*) for multiple characters or the question mark (?) for a single character. For example, typing "*Smith" as a criterion will count any name ending in Smith.
How is using COUNTIFS better than using a PivotTable for this task?
A standard PivotTable typically only displays items that exist in the source data. If a staff member has zero ordered tests, they will not appear in the PivotTable at all. Using a fixed roster alongside COUNTIFS ensures everyone is listed, returning a clear '0' for those with no records.
