Countif percentage of cell containing text or if statement to produce value in specific cells

[Chantilly]

Hi there!

I am trying to calculate the average of cells based on text criteria. I have to use the same column for the data I'm pulling, however, for each value I need, it requires a different set of cells within that column and I cannot get the formula to work if I don't use the whole range.

Breakdown:

Column L contains the words either "Accelerated" or "Non-Accelerated", range L2:L10

I need to EITHER know 1) the COUNTIF percentage of "Accelerated" out of the select cells OR 2)an IF statement that will return the value of "Accelerated" if the percentage or occurance of text is greater than 51%.

Problem:
Within L2:L10, I may need only L2:L5, L8 and L10 for the data I need to calculate, but when putting in the different cells, I am not getting the values needed.

Example for percentage of text that DOES work for range:
=COUNTIF(L2:L10,"Accelerated")/COUNTA(L2:L10)

Example for percentage of text that DOES NOT work for specific cells in the range:
=COUNTIF((L2:L5,L8,L10),"Accelerated")/COUNTA(L2:L5,L8,L10)

Table example:
[TABLE="width: 182"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]Accelerated[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]Non-Accelerated[/TD]
[/TR]
[TR]
[TD]Melissa[/TD]
[TD]Accelerated[/TD]
[/TR]
[TR]
[TD]Kennedy[/TD]
[TD]Non-Accelerated[/TD]
[/TR]
[TR]
[TD]Lauren[/TD]
[TD]Accelerated[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]Non-Accelerated[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]Accelerated[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]Accelerated[/TD]
[/TR]
[TR]
[TD]Linda[/TD]
[TD]Non-Accelerated[/TD]
[/TR]
</tbody>[/TABLE]


Now, if I am putting the parentheses in wrong or using the wrong rules for this, please help me out!

On the flip side, if someone knows how to put an IF statement in that will calculate the percentage of text greater than 51% for specific cells that will return the word "Accelerated" or "Yes", then I'm all ears!

Please help!!!

THANKS!

 

[arunsjain]

Hi,

Try this

=SUM(COUNTIF((L2:L5),"Accelerated"),COUNTIF((L8),"Accelerated"),COUNTIF((L10),"Accelerated"))/COUNTA(L2,L3,L4,L5,L8,L10)

With IF statement

=IF(SUM(COUNTIF((L2:L5),"Accelerated"),COUNTIF((L8),"Accelerated"),COUNTIF((L10),"Accelerated"))/COUNTA(L2,L3,L4,L5,L8,L10)>51%,"Accelerated","")

 

[DanteAmor]

Try



=IF((COUNTIF(L2:L5,"Accelerated")+COUNTIF(L8,"Accelerated")+COUNTIF(L10,"Accelerated"))/COUNTA(L2:L5,L8,L10)>0.51,"Yes","No")