Sum of numbers within sentences




I was wondering how to count numbers set within sentences in a column.

For example:

Example text: Package 1 (2), Addon 1 (2)
Example text: Package 2 (2), Addon 1 (4)
Example text: Package 2 (1)


The goals is to write a formule that counts up the amount of the packages and addons in this case.


Can anyone help me out?



1 Reply
best response confirmed by Jplug (Visitor)


Here's a solution with REDUCE to consider:


items  'dynamic range for your list of items in column A.  
=LET(list, Sheet1!$A$2:$A$10000, nonblank, COUNTA(list), TAKE(list, nonblank))

Then a bit of 'balloon folding' must be done to prep the data to be summarized.

=TEXTSPLIT(TEXTJOIN(",", , items), , ",")

ItemsCleaned - an array of items without the quantities
=TRIM(TEXTBEFORE(ItemsVector, " ("))

ItemQty - an array of quantities without the item names
=TEXTBEFORE(TEXTAFTER(ItemsVector, "("), ")") * 1

header  - used as the initial value in REDUCE
={"Item", "Qty"}

Lambda - Total  - this function runs a unique list of items through a filter to obtain the quanties and then stacks them.
=LAMBDA(a,v,LET(filtered, FILTER(ItemQty, ItemsCleaned = v), VSTACK(a, HSTACK(v, SUM(filtered)))))

Sheet level formula: