Function Problems

How to Filter Sizes and Sum Quantities Using Excel Formulas

Rana Garcia Sep 28, 2026 869 views

Question details

The user needs to extract a dynamic list of unique clothing sizes from Microsoft Forms responses and sum the corresponding quantities, specifically handling numerical quantities stored as text.

How to Filter Sizes and Sum Quantities Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Processing automated form responses where numerical data (quantities) might be exported as text with a leading apostrophe, requiring formulas that automatically update as new rows are added.
Observed behavior: Standard sum functions fail to calculate totals for numbers formatted as text. A combination of array formulas is needed to dynamically filter the sizes and forcefully convert the text-quantities into numbers for accurate summation.

Before you start

Ensure your form response data is formatted as an Excel Table (e.g., Table1) so that your formulas automatically expand to include new form submissions.

Solution 1Recommended

Extract Unique Sizes and Sum Quantities Using Array Formulas

Use the UNIQUE and FILTER functions to generate a dynamic list of sizes, and combine SUM, IF, and a double unary operator to calculate total quantities even when stored as text.

Microsoft Forms often exports numerical data as text by adding a leading apostrophe. To perform math on these values, we must use a double unary operator (--) to convert the text back into readable numbers.

Generate a unique list of sizes

Select an empty cell where you want your list to start. Enter the formula: =UNIQUE(FILTER(Table1[Enter the size of Polo Shirts needed], Table1[Enter the size of Polo Shirts needed]<>"")). This extracts distinct sizes and ignores empty rows.

Set up the sum formula with text conversion

In the cell adjacent to your first unique size (e.g., assuming the unique size is in cell L2), enter the formula: =SUM(IF(Table1[Enter the size of Polo Shirts needed]=L2, --Table1[Enter Quantity of polo Shirts needed3], 0)).

Apply and calculate

Press Enter (or Ctrl+Shift+Enter for older non-dynamic array versions of Excel). The double minus (--) forces Excel to convert the text quantities with leading apostrophes into proper numbers, allowing the SUM function to calculate accurately.

Extract Unique Sizes and Sum Quantities Using Array Formulas

Dynamic Updates: By using structured references like 'Table1[Column Name]', your unique list and sums will automatically update whenever new responses are added to the bottom of the table.

Advanced Formula Support

Process Form Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and FILTER, along with complex data conversion formulas. It makes processing raw form responses and text-formatted data fast and seamless.

1. Open Your Data: Launch WPS Spreadsheet and open the .xlsx file containing your raw form responses.
2. Apply Unique Filters: Select an empty column and type your UNIQUE and FILTER formula to instantly generate a distinct list of your item sizes.
3. Calculate Totals: Use the SUM and IF formula paired with the double unary operator (--) next to your unique sizes to accurately calculate the totals.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports dynamic array functions (UNIQUE, FILTER) out of the boxEasily handles automated table updates and text-to-number conversionsLightweight, fast, and free to use for daily data processing tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do numbers from Microsoft Forms sometimes have a leading apostrophe?

Microsoft Forms often exports numerical data as text to preserve specific formatting or prevent leading zeros from being dropped. Spreadsheet applications represent this text-formatted number by adding a hidden leading apostrophe.

What does the double dash (--) do in an Excel formula?

The double dash, known as the double unary operator, is used to convert non-numeric data types (like TRUE/FALSE boolean values or text representations of numbers) into actual numeric values (1/0 or the number itself) so they can be mathematically processed by functions like SUM.

Why is my UNIQUE formula returning a #CALC! error?

This typically happens if the FILTER function nested within the UNIQUE formula returns an empty array. It means no records meet your specified criteria, or the referenced data range is completely blank.

Excel & Data Problems