COUNTIF with Multiple Criterias and Range

jayjavina

New Member
Joined
Feb 3, 2009
Messages
34
Hi Gurus,

May I please ask for some assistance on how will i be able to get the percentage of each team with members receiving a different set of scores.

I tried this formula and it doesnt seem to work. I dont know what am doing wrong.

COUNTIF(A2:A16,E1)+COUNTIF(B2:B16,">=91")-COUNTIF(B2:B16,">100"))/COUNTIF(A2:A16,E1)

Below is the table

TeamScoresDragonsBullsBeetles
Dragons5681-90
Bulls8871-80
Beetle9576-70
Dragons6960-75
Bulls72
Beetle83
Dragons83
Bulls91
Beetle86
Dragons74
Bulls59
Beetle66
Dragons76
Bulls63
Beetle89

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

Thanks for the help!
Jay
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Does this work for you (requires Excel 2007 or above):

=COUNTIFS($A$2:$A$16,E$1,$B$2:$B$16,">="&LEFT($D2,2),$B$2:$B$16,"<="&RIGHT($D2,2))
 
Upvote 0
.
.

Suppose you wanted the percentage of Dragons that scored over 60. Then you could use:

=COUNTIFS($A$2:$A$16,"Dragons",$B$2:$B$16,">60")/COUNTIF($A$2:$A$16,"Dragons")
 
Upvote 0
I think you want to look at "countifs"

Try something like:

=(countifs(a2:A16,E1,B2:B16,">=91")-countifs(a2:A16,E1,B2:B16,">100"))/COUNTIF(A2:A16,E1)

 
Upvote 0
Thanks for the reply Andrew. But how do i write it with the percentage inside the formula without using the LEFT/RIGHT function?

The actual title for the range is - Percentage of players who received the score if 81%-90%.
My actual volume is more than 1000 for this calculation alone.

DragonsBullsBeetles
Percentage of players who received the score if 81%-90%.0
Percentage of players who received the score if 71%-80%.0
Percentage of players who received the score if 66%-70%.0
Percentage of players who received the score if 50%-65%.0

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

The My apologies Steve, its a typo.

Thank peacock, but there is a specific range of scores.

Lemme try that par
 
Upvote 0
I believe it is simply a formatting issue at that point. Try formatting the number as a % You have them set to no decimal places so everything rounds to zero
 
Upvote 0
That's alright Andrew. I'll use that as an alternative. Thank you.

Thanks Par, I tried your technique and it worked. I never thought that you can use the same criteria twice for this type of function.

Thanks all, appreciate the help.
 
Upvote 0
Glad you got it to work.

This board is amazing because there is almost always a solution.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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