New Contributor

# How to set up a formula to count the frequency?

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 Replies

# Re: How to set up a formula to count the frequency?

@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)``

# Re: How to set up a formula to count the frequency?

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