Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Count how many times certain text combinations occurs in certain columns

I have a data set with multiple columns and roughly 1000 rows. I need to find out how many times certain combinations of columns can be found within the data set.

In my example below, columns A:B represents the raw data set. In C2 I have a formula that finds all non-unique combinations from columns A:B. What I need is a formula that counts how many times combinations in columns C:D are found within columns A:B. The desired output should be in ColE.

Pleas see this example!

Solution

  • you can do it all in one go... delete columns C, D, E and use this formula:

    =ARRAYFORMULA(QUERY({A2:B, A2:A&B2:B}, 
 "select Col1,Col2,count(Col3) 
  where Col1 is not null 
  group by Col1,Col2 
  order by count(Col3) desc 
  label count(Col3)''"))

    0

    for a selected combination only use this formula in E2 cell:

    =ARRAYFORMULA(IFERROR(VLOOKUP(C2:C&D2:D, QUERY({A2:A&B2:B}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1  
  label count(Col1)''"), 2, 0)))

    0