How to Summarize Grades in Excel Using UNIQUE and COUNTIF
Question details
The user wants to create a single-cell text summary of grades by extracting a unique grade list and counting how many times each grade appears.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing student grades or evaluation metrics into a quick, readable text string within a single cell.
- Observed behavior
- The user needs a dynamic way to generate a unique list of grades, tally the occurrences of each, and concatenate the final output together.
Verify that your version of Excel or WPS Spreadsheet supports dynamic array formulas like UNIQUE. Ensure your grade data range is continuous and does not contain unintentional blank cells.
Use UNIQUE, COUNTIF, and CONCAT to Generate a Grade Summary
Extract unique grades to a helper column, count their occurrences, and combine the results into a single text string.
This method breaks down the problem into three logical steps: finding the unique grades, counting them, and merging the text. It is easier to troubleshoot and adjust formatting when using helper columns.
Select a blank cell (e.g., B2) and enter the formula =UNIQUE(A2:A20), assuming your grades are in column A. Press Enter to spill the unique grade list down the column.
In the adjacent cell (e.g., C2), type the formula =COUNTIF(A2:A20, B2#). The '#' symbol ensures the formula automatically applies to the entire spilled unique array, giving you the count for each grade.
In the cell where you want the final text summary, use the CONCAT function to merge the data. Type =CONCAT(B2, ": ", C2, ", ", B3, ": ", C3) and continue for the remaining grades. Alternatively, use =TEXTJOIN(", ", TRUE, B2:B6 & ": " & C2:C6) for a more automated text merge.
Easily Summarize Data Using Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like UNIQUE, COUNTIF, and TEXTJOIN. You can effortlessly manage your grading data, generate summaries, and handle complex formulas seamlessly in a lightweight environment.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the document containing your grading data.
- 2. Apply the UNIQUE and COUNTIF functions: Select an empty cell and type =UNIQUE(range) to extract distinct grades. In the next cell, use =COUNTIF(range, criteria) to tally each grade.
- 3. Combine data with TEXTJOIN: Use the =TEXTJOIN function to merge the unique grades and their counts into a single beautifully formatted summary cell.
Frequently Asked Questions
Why is the UNIQUE formula returning a #NAME? error?
The #NAME? error typically appears if your current spreadsheet software version does not support dynamic array functions. You need a newer version like Excel 365, Excel 2021, or the latest version of WPS Office to use the UNIQUE function.
How can I exclude blank cells from my unique grade summary?
To prevent empty cells from appearing as a '0' or blank in your summary, nest the FILTER function inside the UNIQUE function. Use =UNIQUE(FILTER(A2:A20, A2:A20<>"")) to extract unique values only from non-empty cells.
How do I sort the summarized grades in alphabetical order?
You can easily sort the output by wrapping the SORT function around your UNIQUE formula. Use =SORT(UNIQUE(A2:A20)). If you are using the all-in-one TEXTJOIN formula, replace UNIQUE(A2:A20) with SORT(UNIQUE(A2:A20)) in both parts of the formula.
