How do I calculate the total number of times a piece of text occurs in a range in Google Sheets using a formula?

Question

How do I calculate the total number of times a piece of text occurs in a range in Google Sheets using a formula? Link to Google Sheets: https://docs.google.com/spreadsheets/d/161UHaeiMJ_FNdRErgkdnUqEvbBUutqdq1uHat7GjXXA/edit

Answer 1

try:

=QUERY(FLATTEN(SPLIT(TEXTJOIN(", ", 1, B2:L5), ", ")), 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''")