Forum Discussion
Solved
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: ...
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)
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)