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>

 

[Snakehips]

Thanks,

Do you know if the sum/sumif formula's will work if they are on a different marksheet, the formula im using now comes back #N/A

Clare, could be just a typo giving a mismatch of ranges. Missing 7


=SUMPRODUCT(((Sheet1!F2:F237="A")+(Sheet1!F2:F237="P")+(Sheet1!F2:F237="S")*Sheet1!P2:P237/SUMPRODUCT((Sheet1!F2:F237="A")+(Sheet1!F2:F237="P")+(Sheet1!F2:F237="S"))))

 

[Aladin Akyurek]

Thanks,

Do you know if the sum/sumif formula's will work if they are on a different marksheet, the formula im using now comes back #N/A

The formula is -

=SUMPRODUCT(((Sheet1!F2:F237="A")+(Sheet1!F2:F237="P")+(Sheet1!F2:F23="S")*Sheet1!P2:P237/SUMPRODUCT((Sheet1!F2:F237="A")+(Sheet1!F2:F237="P")+(Sheet1!F2:F237="S"))))

F2:F237 IS STATUS AND P2:P237 IS COMBINED SCORE

Thanks

Clare

SumIfs/CountIfs will work with ranges on a different sheet. If you are talking about a different workbook, that's a different matter.

 

[CLARE83]

Thanks all for all your help it is appreciated. Just one last question, I wanted to add an additional criteria into the formula. If using the formula above (by Tony) (SUMPRODUCT) I want to find the average of - If column A = JAN & Column B= A
+P the average would be ?

Below is the data

MONTH	STATUS	Combined SCORE
JAN	A	27
FEB		25
MAR		29
APR		29
MAY		33
JUN	P	19
JUL		21
AUG		21
JAN	A	29
FEB		29
MAR		27

<colgroup><col width="234" style="width: 176pt; mso-width-source: userset; mso-width-alt: 8334;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

Hope this makes sense.

Kind regards

Clare

 

[Aladin Akyurek]

Why SumProduct?

 

[CLARE83]

Purely because the sumproduct formula above worked, If there is another one that can be used?!?!!?

 

[Aladin Akyurek]

Purely because the sumproduct formula above worked, If there is another one that can be used?!?!!?

See:
https://dl.dropboxusercontent.com/u/65698317/CLARE83.xlsx

 

[CLARE83]

Thank you Aladin that's really helpful, can I still use the same formula if I wanted to add another condition (column A - for a particular month)? Fairly new to multiple conditions and finding it hard to get the right formula with out an error message coming back.

Thanks again

Clare

 

[Aladin Akyurek]

Thank you Aladin that's really helpful, can I still use the same formula if I wanted to add another condition (column A - for a particular month)? Fairly new to multiple conditions and finding it hard to get the right formula with out an error message coming back.

Thanks again

Clare

Sure. For JAN we get:

=SUM(SUMIFS(C2:C11,A2:A11,"JAN",B2:B11,{"A","P"}))/MAX(1,SUM(COUNTIFS(A2:A11,"JAN",B2:B11,{"A","P"})))

=IFERROR(SUM(SUMIFS(C2:C11,A2:A11,"JAN",B2:B11,{"A","P"}))/SUM(COUNTIFS(A2:A11,"JAN",B2:B11,{"A","P"})),"")

 

[CLARE83]

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

 

[Andrew Poulsom]

What you tried is a space character. Blank is "".