Getting the unique values and counts of them in a column of comma separated values in Google Sheets without using JOIN?

=UNIQUE(TRANSPOSE(SPLIT(REGEXREPLACE(TRANSPOSE( QUERY(ARRAYFORMULA(","&A1:A),,5000000))," ,",","),","))) 

but maybe you need this (?):

=QUERY(TRANSPOSE(SPLIT(REGEXREPLACE(TRANSPOSE( QUERY(ARRAYFORMULA(","&A1:A),,5000000))," ,",","),",")), "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''") 

I did a google scripting solution because I wanted to play with key map pairs.

function myFunction() { var myMap = {"candy":0}; var sh = SpreadsheetApp.getActiveSpreadsheet(); var ss = sh.getSheetByName("FIRSTSHEETNAME"); var os = sh.getSheetByName("Ingredients"); var data = ss.getDataRange().getValues(); for (var i=0; i<data.length;i++)//full //for (var i=1; i<4000;i++)//test { var array = data[i][0].split( ","); for (var j=0; j<array.length;j++) { var item = array[j]; //Logger.log(array[j]); if (myMap[item]>-1){ //Logger.log("REPEAT INGREDIENT"); var num = parseInt(myMap[item]); num++; myMap[item]=num; //Logger.log(item +" "+num); } else { myMap[item]=1; //Logger.log("New Ingredient: "+item); //Logger.log(myMap); } } } //Logger.log(myMap); var output=[]; for (var key in myMap){ //Logger.log("Ack"); output.push([key,myMap[key]]); } //Logger.log(output); os.getRange(2,1,output.length,output[0].length).setValues(output); } 

You'll need to add an "Ingredients" tab for the output and change your first tab to be called FIRSTSHEETNAME (or change the code). In my testing it took 4 seconds for 4 items, 5 seconds for 400 items, and 6 seconds for 4000 items. there might be an issue with leading spaces but this gives you a place to start.

A fast running formula that works with columns of at least 40,000 rows:

 =query(arrayformula(TRIM(flatten(split(A2:A20000,",")))),"select Col1,Count(Col1) Where NOT (Col1='' OR Col1 contains '#VALUE!') Group By Col1 order by Count(Col1) desc label Col1 'Ingredient',Count(Col1) 'Freq.'") 

FLATTEN function, combined with SQL (QUERY function) can be a solution for fast filtering of values (such as empty or error messages).

TRIM function avoids artifacts in the result due to meaningless spaces before/after each string.

Sheet: https://docs.google.com/spreadsheets/d/1m9EvhQB1Leg2H7L52WhPe66_jRrTc8VsnZcliQsxJ7s/edit?usp=sharing

*In case of false case differences, you could normalize all characters of the strings to uppercase before within the same formula with UPPER(A2:A20000).