Search code examples
google-sheets

Count the number of string matches against a table entry inside a Google Sheets cell

I am trying to find a good way to organise the following data. Attached is the screenshot of what the data roughly looks like:

enter image description here

As you can see, the "Order" column has multiple line breaks where each line break indicates one item. I am trying to work out the total cost for each individual, based on the item's price on the bottom right table.

My strategy currently is:

  1. Count how many times the name of the item (Column E) appears inside each order cell (Column B)
  2. Multiply the number of occurrences by the price per item (Column F).

I am currently stuck at step 1. My attempts using FIND and COUNTA/COUNTUNIQUE does not give me the answer I want. Any suggestions?

Solution

  • You can try with this approach:

    It splits the value by CHAR(10), which is the breakline, and uses BYCOL to find the value in which the string appears (with REGEXMATCH). Then sums those values

    BYROW lets you repeat the process over all of the orders

    =BYROW(B2:B,LAMBDA(order,IF(order="","",
 SUM(BYCOL(SPLIT(order,CHAR(10),1,1),LAMBDA(each,
  FILTER(F2:F,REGEXMATCH(each,E2:E),E2:E<>"")))))))

    enter image description here