Share via

Monthly we collate items bought for our manufacturing franchise for KPi stats.

Andre Hofer 85 Reputation points
2026-04-15T03:08:02.67+00:00

Monthly we collate items bought for our manufacturing franchise to produce KPI stats. Currently use a combo two step of identifying all similar items with a “search” and give them a label and then aggregate these labels with a sum(filter). The number of variables to combine are growing and looking for a simpler solution.

trying this formula:

=LET(item,J5:J35,QuantitySold,L5:L35,keys,{"ASS SMALL","ASS CRATE","ASS TALL"},SUMIFS(item,QuantitySold,keys)) but gives a spill answer

where the table looks like:

User's image

Here we are combining all “assembly" totals. The item table can be several 100 items. Is there a way to fix this formula or is there a better formula. The number of variables that can be categorised with a label can some times be up to 30. So would prefer to set up an array for “keys”.

Microsoft 365 and Office | Excel | For business | MacOS

Answer accepted by question author

AI answer

2026-04-15T03:08:22.4533333+00:00

SUMIFS does not accept an array of criteria in a single argument, so using keys, {"*ASS SMALL*","*ASS CRATE*","*ASS TALL*"} in one SUMIFS call will always spill an array of results instead of one total.

A pattern that works is to calculate one SUMIFS per key and then sum those results. With LET and an array of keys this can be written compactly:

=LET(
    items, J5:J35,
    qty,   L5:L35,
    keys,  {"*ASS SMALL*";"*ASS CRATE*";"*ASS TALL*"},
    SUM( SUMIFS(qty, items, keys) )
)

Key points:

  • keys is a vertical array (; between items). SUMIFS(qty, items, keys) returns one subtotal per key, which spills.
  • Wrapping that in SUM(...) adds those subtotals into a single result.
  • items is the range with the item descriptions (e.g., “ASS CRATE - Crate for shipping”).
  • qty is the range with QuantitySold.

To extend this to more patterns, just add more entries to keys:

keys, {"*ASS SMALL*";"*ASS CRATE*";"*ASS TALL*";"*ASS PANELS*";"*ASS XYZ*"}

This keeps all the category patterns in one place and avoids helper columns while still using standard SUMIFS behavior with wildcards.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.