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

Advanced if function??
 

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

Advanced if function??
 

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