Search code examples
google-sheetspivot-tablegoogle-sheets-formulagoogle-sheets-query

How can I COUNT instances of Value next to another Value

As a game designer, I am working on Google Sheets to make collections of animals with specific outfits. I need to make sure I use all combinations are evenly used.

I could just use for-loop, but scripting here would add unnecessary complexity for the whole process and it would be nice to just do it in the Sheets like where I make the data needed for our game. I tried different combinations of MATCH, INDEX and COUNTIFS-functions. Dabbled a bit with Pivot Tables and QUERY, but my brain is stuck and I could use some help.

My data looks something like this:

Collection1:[Animal1][Outfit1][Animal1][Outfit7][...]
Collection2:[Animal6][Outfit3][Animal2][Outfit18][...]
[...]

So rows for collections with animal FOLLOWING the outfit it has in the next cell.

I'd like to have a sheet with Rows of animals with Columns for Outfits and Counts for those Outfits for that Animal.

I can't brain this and any hints would help. Sheets can be so unintuitive for me.

Solution

  • cell J1:

    =QUERY({B:C;D:E;F:G}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1) 'animal count'")

    cell J6:

    =QUERY({B:C;D:E;F:G}, 
 "select Col2,count(Col2) 
  where Col1 is not null 
  group by Col2 
  label count(Col2) 'outfit count'")

    cell J12:

    =QUERY({B:C;D:E;F:G}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  pivot Col2")

    0