Averageifs formula - can you search for two different criteria in one criteria range

[CLARE83]

Hi,

I am working on spreadsheet in which I have a column of data (combined score) for which I need to find the average off if they match 2 different criteria's (status - A and P)in a different column.

Below is an example of the data.

Can anyone advise of the formula I keep hitting a brick wall!!!

Thanks in advance

Month	STATUS	Combined Sccore
JAN	N	31
FEB		31
MAR		25
APR		27
MAY	A	27
JUN	P	11
JULY	P	23
AUG		25
SEP		27
OCT		25

<tbody>
</tbody>

 

[CLARE83]

I've tried both ways it doesn't pick it up

 

[Andrew Poulsom]

Please post the formula that's not working.

 

[Aladin Akyurek]

Hi all, using the above formula how would I count blank cells? I have tried " " and this is not picking the cell up?

Thanks in advance

Clare

Another question: Blank cells of which range?

 

[CLARE83]

Data Sample -

NAME​

SEX​

Rep​

GROUP​

H​

TYPE​

EL​

PA​

DT​

%​

EX6​

KS2 1​

POINT VALUE​

KS2 2 ​

POINT VALUE ​

Combined​

Ability​

AB

M

9EL

9EL/Gg

BOS

87.7

4b

27​

4b

27

27 ​

L

BC

M

9EL

9EL/Gg

BOS

N

99.4

4b

25​

4b

27

25​

L

CD

M

9EL

9EL/Gg

BOS

83.7

4a

29​

4a

29​

29​

U

DE

F

9EL

9EL/Gg

BOS

91.1

5b

33​

4b

27​

29 ​

U

EF

F

9EL

9EL/Gg

BOS

Y

98.4

5b

33​

5a

35​

33​

U

<tbody> </tbody>

Formula used - =SUMPRODUCT((('Y9 DATA'!$F$2:$F$237=" ")+('Y9 DATA'!$F$2:$F$237="N"))*'Y9 DATA'!$P$2:$P$237/SUMPRODUCT(('Y9 DATA'!$F$2:$F$237=" ")+('Y9 DATA'!$F$2:$F$237="N")))
Any ideas?

 

[Andrew Poulsom]

You have a misplaced closing parenthesis there:

=SUMPRODUCT((('Y9 DATA'!$F$2:$F$237="")+('Y9 DATA'!$F$2:$F$237="N"))*'Y9 DATA'!$P$2:$P$237)/SUMPRODUCT(('Y9 DATA'!$F$2:$F$237="")+('Y9 DATA'!$F$2:$F$237="N"))

 

[CLARE83]

Thank you so much Andrew! I couldn't see that for looking!!!