Home > front end >  GOOGLE SHEET SUM by Split Cell Text
GOOGLE SHEET SUM by Split Cell Text

Time:04-28

Trying to find a easy solution to summarise each type of building material in a table like below:

A(id) B(WallType) C(Quantity))
1 brick, drywall 25
2 drywall, stud, drywall 10
3 drywallFire, stud, drywallFire 20
4 drywallFire, drywallFire, stud, drywallFire, drywallFire 30

The column B is a combination of material code that join by ", ". For example,

  • Row 2 means drywall on both side, this row's drywall quantity should be 20 (10x2).
  • Row 3 is multi-layers fire rated drywall, drywallFire should be 120 (30x4).

Is there a formula combination to get a list (unique) of material and quantity?

Material Sum
brick 25
drywall 45
drywallFire 160
stud 60

CodePudding user response:

use:

=INDEX(QUERY(SPLIT(TRIM(FLATTEN(SPLIT(B2:B, ",")&"♦"&C2:C)), "♦"),
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''"))

enter image description here

  • Related