# COUNTIF with Multiple Criterias and Range

#### jayjavina

##### New Member
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

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Does <60 or >90 not count?

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

.
.

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

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)

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

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

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

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.

Glad you got it to work.

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

Replies
1
Views
204
Replies
5
Views
833
Replies
6
Views
238
Replies
8
Views
559
Replies
1
Views
138

Threads
1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

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

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