Forum Discussion

vvicvicc's avatar
vvicvicc
Copper Contributor
Jul 09, 2020

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? 

excel
Formulas and Functions

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 09, 2020

    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)

     

Resources