I have the following query: E.g. I have 10 cells with either of the following values: "High" "Medium" "Low" I need an IF condition where I could fill another cell based on the number of occurances of "High", "Medium", "Low". So, if the out of those 10 cells, "High" occurs more number of times than "Medium" or "Low", then fill a particular selected cell with "H"

Hi, There are likely several ways to do this. Here is one way. In cells B3 to B12 are your 10 input statuses Cells C3 to C5 provide a count of the number of occurences of each status C3 contains =COUNTIF($B$3:$B$12,D3) and C4, C5 follow the same pattern, taking the value to be counted from the cells in column D (The words must be identical to those used in the ten entries in column B - use data validation to ensure entries are valid) Cell E3 reports the most frequent. The formula in E3 is =VLOOKUP(MAX(C3:C5),C3:D5,2,FALSE) B C D E Inputs Counts Status Result 3 High 4 High Medium 4 Medium 5 Medium 5 High 1 Low 6 Medium 7 Medium 8 High 9 High 10 Medium 11 Low 12 Medium There is a problem in that you can get two statuses present in equal numbers In the above solution if High=5 and Low=5 for example, the result returned will be High If you reversed the order of the statuses in column D with Low first, the result would be Low as Vlookup returns the first match it finds. If you wanted a different output when two statuses occurred at the same frequency you will have to define the logic for the outcome you want. Regards

Thanks. I also created a code using if and countif...seems to work as of now :) cheers!!!

What are you looking for if there is a tie? As Humar says, there are multiples ways to solve this. This will handle 10 - or any even number of - cells since there can't be a tie between more than 2 values. If you use a range of cells that is divisble by 3, there could be a three way tie. You could easily add a condition to hanlde that. =IF(AND(COUNTIF(A1:A10,"High")>COUNTIF(A1:A10,"Medium"),COUNTIF(A1:A10,"High")>COUNTIF(A1:A10,"Low")),"H",IF(AND(COUNTIF(A1:A10,"Medium")>COUNTIF(A1:A10,"High"),COUNTIF(A1:A10,"Medium")>COUNTIF(A1:A10,"Low")),"M",IF(AND(COUNTIF(A1:A10,"Low")>COUNTIF(A1:A10,"Medium"),COUNTIF(A1:A10,"Low")>COUNTIF(A1:A10,"High")),"L",IF(COUNTIF(A1:A10,"High")=COUNTIF(A1:A10,"Medium"),"Tie Between H & M",IF(COUNTIF(A1:A10,"High")=COUNTIF(A1:A10,"Low"),"Tie Between H & L","Tie Between M & L")))))

Thanks for your reply...What I have done is to select the higher value in case of a tie. For e.g. If there is a tie between High and Medium, select High as the answer...This has been done in accordance with business requirements.... Thanks a lot people