How to set up a formula to count the frequency?

%3CLINGO-SUB%20id%3D%22lingo-sub-1512290%22%20slang%3D%22en-US%22%3EHow%20to%20set%20up%20a%20formula%20to%20count%20the%20frequency%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512290%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I'm%20going%20to%20analyze%20the%20ingredients%20in%20different%20food%20products.%20I%20have%20put%20each%20product%20on%20a%20different%20row%2C%20with%20each%20ingredient%20in%20a%20single%20cell.%20And%20some%20products%20have%20more%20ingredients%20while%20some%20have%20a%20few%20only.%20How%20can%20I%20create%20a%20list%20showing%20all%20the%20ingredients%20appearing%20in%20the%20whole%20spreadsheet%20and%20then%20find%20the%20frequency%20of%20each%20ingredient%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512290%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512532%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20set%20up%20a%20formula%20to%20count%20the%20frequency%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723823%22%20target%3D%22_blank%22%3E%40vvicvicc%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BIF%20you%20have%20the%20new%20array%20functions%20you%20should%20be%20able%20to%20use%20(based%20on%20the%20ingredients%20being%20in%20the%20range%20%24B%242%3A%24Z%24200)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DUNIQUE(%24B%242%3A%24Z%24200)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Enote%3A%20this%20will%20fill%20down%20in%20the%20column%20you%20place%20the%20formula%3C%2FP%3E%3CP%3EIf%20you%20(like%20me)%20don't%20have%20those%20array%20functions%20yet%20you%20can%20use%20this%20formula%20I%20created%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(OFFSET(%24A%241%2CMOD(AGGREGATE(15%2C7%2C%20(ROW(%24B%242%3A%24Z%24200)%2B100000*COLUMN(%24B%242%3A%24Z%24200))%2F(--(COUNTIF(AA%242%3AAA2%2C%24B%242%3A%24Z%24200)%3D0))%2C%201)%2C100000)-1%2CINT(AGGREGATE(15%2C7%2C%20(ROW(%24B%242%3A%24Z%24200)%2B100000*COLUMN(%24B%242%3A%24Z%24200))%2F(--(COUNTIF(AA%242%3AAA2%2C%24B%242%3A%24Z%24200)%3D0))%2C%201)%2F100000)-1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ealso%20assuming%26nbsp%3B%20ingredients%20being%20in%20the%20range%20%24B%242%3A%24Z%24200%20and%20you%20paste%20this%20in%20AA2%20and%20fill%20down%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20have%20the%20above%20UNIQUE%20list%20you%20can%20use%20COUNTIF()%20in%20the%20next%20column%20(col%20AB%20in%20this%20case)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIF(%24B%242%3A%24Z%24200%2CAA2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514642%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20set%20up%20a%20formula%20to%20count%20the%20frequency%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much%20!!!!%20it%20took%20me%20days%20to%20find%20the%20solution!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

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

 

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