Function Problems

How to Use Excel Formulas to Count Items and Calculate Totals by Name

Camila Milosovich Sep 25, 2026 870 views

Question details

The user needs to count the occurrences of specific names or categories, calculate their corresponding total values, and determine their percentages of the overall count and sum.

How to Use Excel Formulas to Count Items and Calculate Totals by Name

Product: Excel
Device & OS: Desktop
Scenario: Analyzing work-order or sales data to determine how many jobs each person completed and the total money earned per person.
Observed behavior: The goal is to set up dynamic summary formulas that automatically include new names and amounts when new rows are added to the list.

Before you start

Before applying your formulas, ensure your dataset is organized in clear columns without blank headers. Consider formatting your data as an Excel Table (Ctrl+T) so your referenced ranges expand automatically when new records are added.

Solution 1Recommended

Create a Dynamic Summary Table using UNIQUE, COUNTIF, and SUMIF

This method automatically extracts a distinct list of names and dynamically calculates the corresponding counts, totals, and percentages without manual updates.

By combining modern dynamic array functions with traditional conditional math, you can build a reporting table that updates in real-time. We assume names are in column B and amounts are in column A.

Extract Unique Names

Click cell D2 and type `=SORT(UNIQUE(FILTER(B2:B100, B2:B100<>"")))`. This formula filters out blank cells, removes duplicate names, and sorts the remaining names alphabetically.

Count Items per Name

In cell E2, enter `=COUNTIF(B:B, D2)` to count how many times the name in D2 appears in column B. Drag the fill handle down to apply this formula to the rest of the generated names.

Calculate Total Amount per Name

In cell F2, type `=SUMIF(B:B, D2, A:A)` to sum all values in column A that correspond to the name in D2. Drag this down to complete the column.

Calculate Percentages

To find the item count percentage for a name, use `=E2/COUNTA(B:B)`. For the total sum percentage, use `=F2/SUM(A:A)`. Format these result cells as percentages using the Number Format dropdown on the Home tab.

Create a Dynamic Summary Table using UNIQUE, COUNTIF, and SUMIF

Pro Tip: Using full column references (like B:B and A:A) ensures that newly added rows are instantly included in your calculations without adjusting the formula.

Solution 2

Calculate Totals for a Specific Person Manually

If you only need to look up data for one or two specific individuals (e.g., James or Lewis), you can insert their names directly into the formulas.

Advanced Data Summarization

Calculate Totals and Analyze Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions including COUNTIF, SUMIF, and dynamic arrays like UNIQUE. It provides an intuitive environment for summarizing large datasets without requiring complex coding.

1. Open Your Dataset: Launch WPS Spreadsheet and open your existing workbook containing the names and amounts you wish to summarize.
2. Apply Summarization Formulas: Select an empty cell and type your `=SUMIF()` or `=COUNTIF()` formula to extract specific totals based on your criteria.
3. Quickly Fill Data: Click the small square at the bottom-right corner of your active cell and drag it down to quickly calculate totals for the rest of your list.
4. Format as Table: Highlight your data and press Ctrl+T to convert it into a Table, ensuring your formulas dynamically expand alongside new entries.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports modern dynamic array formulas (UNIQUE, SORT, FILTER) out-of-the-box.Free and lightweight software for comprehensive data analysis and reporting.

Frequently Asked Questions

How do I ensure my formulas automatically update when new names are added?

Formatting your dataset as an Excel Table (by pressing Ctrl+T) or using full-column references (like A:A and B:B in your formulas) ensures any new data appended to the bottom automatically feeds into your SUMIF and COUNTIF calculations.

Why does my UNIQUE function return a '0' or blank space?

This usually happens if your referenced range includes empty rows. You can remove blanks by nesting the FILTER function inside UNIQUE, like this: `=UNIQUE(FILTER(B2:B100, B2:B100<>""))`.

Can I sort the unique list of names alphabetically?

Yes, you can easily alphabetize the results by wrapping the SORT function around your formula. For example, `=SORT(UNIQUE(B2:B100))` will generate a distinct list that is automatically sorted from A to Z.

How do I calculate the percentage of a specific category against the grand total?

To find the percentage, divide the category's SUMIF result by the overall SUM of the entire column. The formula looks like this: `=SUMIF(B:B, "James", A:A) / SUM(A:A)`. Once entered, change the cell's format to Percentage.

Excel & Data Problems