CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
Stathis Karathanasis
Stathis Karathanasis

Reputation: 25

Search different columns and count specific values in Google Sheets

My Google Sheet looks like this:

enter image description here

I want the H , I and J columns to be filled of the data of the A:F columns. The data will be inserted using a form. Every row (A:F) is a day. The C column separate the text items using the ",". It will be more than iron, glass or wood. This is just an example. The countries (G column) are pre-edited.

I want when a new day (row) is inserted to look for the iron, glass and wood that every country bought and if it finds the item to add 1 under the matched one, so I will know that France buys more glass than Iron and zero wood.

Upvotes: 1

Views: 194

Answers (1)

player0
player0

Reputation: 1

G7 cell:

={"countries"; "australia"; "america"; "greece"; "france"; "norway"; "edypt"; "russia"}

0

H7 cell:

={"glass","iron","wood"; 
 ARRAYFORMULA(IF(G8:G="",,IFNA(VLOOKUP(G8:G, 
 QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 IF(IFERROR(SPLIT({C7:C; F7:F}, ","))<>"", 
 "♦"&{A7:A; D7:D}&"♥"&SPLIT({C7:C; F7:F}, ",")&"♥", ), 
 "where Col1 is not null", 999^99)),,999^99), "♦")), "♥"), 
 "select Col1,count(Col1) group by Col1 pivot Col2"), {2,3,4}, 0))*1))}

0

spreadsheet demo

Upvotes: 1

Related Questions