Advanced if function??

[5tacey]

1 Region A B C D ... G
2 Wales 3 3 2 3
3 England 5 4 2 4
4 England 2 4 4 4 Rating
5 England 1 4 5 1 4
6 Scotland 1 3 3 3 5
7 Scotland 4 1 4 5
8 Wales 2 4 3 5

I would like to create a single formula to calculate a percentage score of
how many rated 4 or 5, from each region. This is just an example dataset, the
real dataset is massive and it's not appropriate to use Auto filters

The formula i tried was =IF(A2:A8="England",COUNTIF(B2:B8,G5:G6),"None")

Basically i need an IF formula to say count all the 4's and 5's in the
column, if the row they're on is England.

Can anyone help??

 

[T. Valko]

i need an IF formula to say count all the 4's and 5's

in the column, if the row they're on is England.

Try this:

=SUMPRODUCT((A2:A8="England")*((B2:F8=4)+(B2:F8=5)))