COUNTIF with Multiple Criterias and Range

[jayjavina]

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

Team	Scores			Dragons	Bulls	Beetles
Dragons	56		81-90
Bulls	88		71-80
Beetle	95		76-70
Dragons	69		60-75
Bulls	72
Beetle	83
Dragons	83
Bulls	91
Beetle	86
Dragons	74
Bulls	59
Beetle	66
Dragons	76
Bulls	63
Beetle	89

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

Thanks for the help!
Jay

 

[steve the fish]

Does <60 or >90 not count?

 

[Andrew Poulsom]

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

 

[ParamRay]

.
.

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

 

[par60056]

ADVERTISEMENT

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)

 

[jayjavina]

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.

	Dragons	Bulls	Beetles
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

 

[Andrew Poulsom]

ADVERTISEMENT

Why don't you have the column with the ranges like you posted and hide it?

 

[par60056]

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

 

[jayjavina]

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.

 

[par60056]

Glad you got it to work.

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