How to set up a formula to count the frequency?

[vvicvicc]

Hi! I'm going to analyze the ingredients in different food products. I have put each product on a different row, with each ingredient in a single cell. And some products have more ingredients while some have a few only. How can I create a list showing all the ingredients appearing in the whole spreadsheet and then find the frequency of each ingredient? 

[#2]

@vvicvicc   IF you have the new array functions you should be able to use (based on the ingredients being in the range $B$2:$Z$200):

=UNIQUE($B$2:$Z$200)

note: this will fill down in the column you place the formula

If you (like me) don't have those array functions yet you can use this formula I created:

=IFERROR(OFFSET($A$1,MOD(AGGREGATE(15,7, (ROW($B$2:$Z$200)+100000*COLUMN($B$2:$Z$200))/(--(COUNTIF(AA$2:AA2,$B$2:$Z$200)=0)), 1),100000)-1,INT(AGGREGATE(15,7, (ROW($B$2:$Z$200)+100000*COLUMN($B$2:$Z$200))/(--(COUNTIF(AA$2:AA2,$B$2:$Z$200)=0)), 1)/100000)-1),"")

also assuming  ingredients being in the range $B$2:$Z$200 and you paste this in AA2 and fill down

Once you have the above UNIQUE list you can use COUNTIF() in the next column (col AB in this case):

=COUNTIF($B$2:$Z$200,AA2)

[#3]

@mtarler thank you so much !!!! it took me days to find the solution!!