Advanced if function??

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

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

5tacey;283055 Wrote:
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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79066

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

--
Biff
Microsoft Excel MVP


"5tacey" wrote in message
...
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??