Forum Discussion

Jplug's avatar
Jplug
Copper Contributor
Feb 09, 2023
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: 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? 🙂

 

 

excel
  • Patrick2788's avatar
    Patrick2788
    Feb 09, 2023

    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)

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 09, 2023

    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)

     

Resources