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

Chantilly

New Member
Joined
Jul 15, 2019
Messages
10
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:
JoeAccelerated
AmyNon-Accelerated
MelissaAccelerated
KennedyNon-Accelerated
LaurenAccelerated
DaveNon-Accelerated
MeganAccelerated
JasonAccelerated
LindaNon-Accelerated

<tbody>
</tbody>


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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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","")
 
Upvote 0
Try



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

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top