Sum of numbers within sentences

[Jplug]

Hi,

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?

[#2 · Accepted solution]

@Jplug 

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.

ItemsVector
=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:
=REDUCE(header,SORT(UNIQUE(ItemsCleaned)),Total)