Forum Discussion
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 s...
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)
