SOLVED

Visitor

# Sum of numbers within sentences

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?

best response confirmed by Jplug (Visitor)
Solution

# Re: Sum of numbers within sentences

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: