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

 

[Simon Lloyd]

Research the use of SUMPRODUCT, a simple way to use it would b
=SUMPRODUCT((A1:A30="England")*($B$2:$G$30=4)

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

I would like to create a single formula to calculate a percentage scor
o
how many rated 4 or 5, from each region. This is just an exampl
dataset, th
real dataset is massive and it's not appropriate to use Auto filter

The formula i tried wa
=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 th
column, if the row they're on is England

Can anyone help?

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

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