How to Count Difficulty Levels by Name using COUNTIFS in Excel
Question details
The user needs to count the occurrences of specific difficulty levels (such as Hard, Medium, Easy) assigned to each person using a multiple-criteria formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary table to tally tasks or items categorized by both person and difficulty.
- Observed behavior
- The goal is to calculate a two-criteria count efficiently to build a clean matrix summarizing task difficulties per individual.
Ensure your dataset is organized into clear columns (e.g., one column for Names, one for Difficulty levels) without interrupting blank rows.
Use the COUNTIFS Function to Count by Two Criteria
This standard approach uses mixed absolute and relative cell references, allowing you to easily copy the formula across a summary table.
The COUNTIFS function allows you to count cells that meet multiple criteria. By correctly locking references (using the $ symbol), you can set up a single formula that handles both rows (names) and columns (difficulty levels).
Create a table with unique names listed down column A and difficulty levels (e.g., Hard, Medium, Easy) arranged as column headers in row 1.
Click on cell B2 and type `=COUNTIFS($J$2:$J$10,$A2,$L$2:$L$10,B$1)`. Adjust the range references ($J$2:$J$10 for the source names and $L$2:$L$10 for the source difficulties) to match your actual data layout.
Press Enter to calculate the first cell. Drag the fill handle (the small square at the bottom-right of the cell) across the columns and down the rows to populate the rest of the summary table.
Use a Spilling COUNTIFS Formula for Dynamic Arrays
For users with modern versions of Excel supporting dynamic arrays, you can output the entire matrix with a single formula.
Generate a Dynamic Summary Table with PIVOTBY
In the newest versions of Excel, the PIVOTBY function instantly creates a grouped count without requiring a pre-built table shell.
Use WPS Spreadsheet to Count Data with Multiple Criteria
WPS Spreadsheet fully supports the COUNTIFS function, dynamic arrays, and advanced pivot tables, making it incredibly easy to summarize complex datasets by multiple conditions like names and difficulty levels.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your names and difficulty levels.
- 2. Set up the summary area: Create a grid layout with unique names in the first column and difficulty ratings as headers in the top row.
- 3. Insert the COUNTIFS formula: Select the first empty data cell and enter the COUNTIFS formula using proper absolute and relative references.
- 4. Apply to all cells: Drag the fill handle to copy the logic across the entire summary table to automatically count all categories.
Frequently Asked Questions
Why is my COUNTIFS formula returning zero or giving an error?
This usually happens if the criteria ranges do not match in size, or if there are trailing spaces in your data. Ensure your ranges (e.g., $J$2:$J$10 and $L$2:$L$10) are exactly the same height.
Can I count more than two criteria with COUNTIFS?
Yes, the COUNTIFS function supports up to 127 range/criteria pairs. You can simply append additional ranges and criteria to the formula separated by commas.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF is designed to evaluate a single condition in one range, while COUNTIFS allows you to check multiple conditions across multiple ranges simultaneously.
