Function Problems

Excel SUMIFS Alternative: Group and Sum Product Sizes by Fit

Algirdas Jasaitis Sep 25, 2026 872 views

Question details

Looking for an Excel formula alternative to SUMIFS to retrieve a customer row and sum their product quantities based on size groups.

Excel SUMIFS Alternative: Group and Sum Product Sizes by Fit

Product: Excel
Device & OS: not provided
Scenario: A user needs to sum customer product quantities sorted into specific size categories (e.g., sizes 6-16 for fit F, and sizes 18-28 for fit C) from a large data table.
Observed behavior: The user wants a dynamic array approach using XLOOKUP and SUM instead of traditional SUMIFS to prevent creating excessive calculation discussion threads in large worksheets.

Before you start

Verify that your size headers are formatted as numbers rather than text strings, and ensure your data layout allows XLOOKUP to fetch the exact row corresponding to the customer name.

Solution 1Recommended

Use SUM and XLOOKUP with Boolean Array Logic

By multiplying the row values returned by XLOOKUP with a boolean logic array, you can dynamically sum categorized columns without relying on SUMIFS.

Traditional SUMIFS formulas require distinct ranges, which can be difficult to manage when searching both rows (customers) and columns (sizes). Using XLOOKUP to extract the entire customer row and applying boolean logic allows you to sum only the sizes that meet your criteria.

This method also avoids creating heavy calculation threads in larger workbooks and works seamlessly with dynamic array engines.

Define the target customer and data range

Identify the cell containing the customer you are searching for (e.g., D9), your customer names range (E3:E5), the numeric size headers (G2:Q2), and the quantity data (G3:Q5).

Apply formula for the first size group (Fit F)

Select the destination cell for your Fit F total. Enter the formula: =SUM(XLOOKUP(D$9,$E$3:$E$5,$G$3:$Q$5)*((--$G$2:$Q$2)<=16)) and press Enter. This will sum all quantities for sizes 16 and under.

Apply formula for the second size group (Fit C)

Select the destination cell for your Fit C total. Enter the formula: =SUM(XLOOKUP(D$9,$E$3:$E$5,$G$3:$Q$5)*((--$G$2:$Q$2)>16)) and press Enter to sum quantities for sizes over 16.

Use SUM and XLOOKUP with Boolean Array Logic

Understanding the Double Unary Operator (--): The double hyphen (--) in the formula converts the TRUE/FALSE results of your size condition into 1s and 0s. This allows the SUM function to correctly multiply and calculate the final quantity for the matching sizes.

Group and Sum Complex Data Efficiently in WPS Spreadsheet

WPS Spreadsheet features a robust calculation engine that fully supports advanced dynamic array functions like XLOOKUP and SUM. You can apply these modern formulas effortlessly to handle complex data grouping tasks.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the product size and customer data.
2. Select the destination cell: Click on the cell where you want the total quantity for the specific size fit to appear.
3. Insert the XLOOKUP and SUM formula: Type the combined formula, utilizing the boolean array logic (e.g., =SUM(XLOOKUP(...)*(--...<=16))), into the formula bar.
4. Calculate the result: Press Enter. WPS Spreadsheet will instantly calculate and return the grouped sum based on your specific fit criteria.

Full support for XLOOKUP and modern dynamic array formulas100% format compatibility with Microsoft Excel (.xlsx) filesLightweight installation with blazing fast calculation speedsFree alternative for professional spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Why use XLOOKUP and SUM instead of SUMIFS for this task?

SUMIFS requires strictly aligned range criteria, making it difficult to use when conditionally evaluating horizontal column headers against a vertical row lookup. XLOOKUP fetches the exact 1D row array, and multiplying it by a boolean condition array lets SUM process the final result cleanly and dynamically.

Will this formula work on older versions of Excel?

The XLOOKUP function is available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version, you may need to use an INDEX and MATCH combination enclosed within a SUMPRODUCT formula to achieve a similar result.

What should I do if the formula returns an error instead of the sum?

Check that your size headers (e.g., G2:Q2) are formatted as actual numbers and not text. Additionally, ensure that the size of your header range exactly matches the column width of your quantity lookup range (G3:Q5).

Excel & Data Problems