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

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Jun 11, 2014

• #2

Welcome to the board. I couldn't get SUMIFS to work, but these two seem to:

Code:

=SUMIF($B$1:$B$10,"A",$C$1:$C$10)+SUMIF($B$1:$B$10,"P",$C$1:$C$10)

Code:

=SUMPRODUCT($C$1:$C$10,--(($B$1:$B$10="A")+($B$1:$B$10="P")))

 

Upvote 0

C

CLARE83

New Member

Joined

Jun 11, 2014

Messages

28

• Jun 11, 2014

• #3

Thanks Jack, however is there a formula that will give me the average score of the data for only A & P? The two provided formulas only gives a total.

Thanks again

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 11, 2014

• #4

CLARE83 said:

Thanks Jack, however is there a formula that will give me the average score of the data for only A & P? The two provided formulas only gives a total.

Thanks again

Click to expand...

Try one of:

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

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

 

Upvote 0

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

5,835

Office Version

1. 365

Platform

1. MacOS

• Jun 11, 2014

• #5

CLARE83,

Welcome to MrExcel.

I don't think you will get AVERAGEIFS working with OR criteria.

Maybe try this....

Excel 2007
	A	B	C	D
1	Month	STATUS	Combined Sccore	Average
2	JAN	N	31	20.33333333
3	FEB		31
4	MAR		25
5	APR		27
6	MAY	A	27
7	JUN	P	11
8	JULY	P	23
9	AUG		25
10	SEP		27
11	OCT		25
Sheet48

Cell Formulas
Range	Formula
D2	=IFERROR(SUMPRODUCT(((B2:B11="A")+(B2:B11="P"))*C2:C11)/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")

Hope that helps.

 

Last edited: Jun 11, 2014

Upvote 0

C

CLARE83

New Member

Joined

Jun 11, 2014

Messages

28

• Jun 11, 2014

• #6

Thanks both, they all work. Can you just break down the formulas for me so I can have a better understanding of how it works?

Thanks

Clare

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 11, 2014

• #7

CLARE83 said:

Thanks both, they all work. Can you just break down the formulas for me so I can have a better understanding of how it works?

Thanks

Clare

Click to expand...

Tony proposes a heavy formula (performancewise)...

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

Supposing that you know how SumIfs and CountIfs, we can be bref. Both SumIfs and CountIfs give one result for each criteria in {"A","P"}. Surrounding Sum totals those results...

So we move from:

SUM/COUNT

to

SUMIFS/COUNTIFS

to

SUM(SUMIFS)/SUM(COUNTIFS)

that is, from an unconditional average to a conditional average.

 

Upvote 0

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

5,835

Office Version

1. 365

Platform

1. MacOS

• Jun 11, 2014

• #8

Aladin Akyurek said:

Tony proposes a heavy formula (performancewise)...

Click to expand...

'Never mind the quality...... feel the weight!!' is my motto.

Clare, here is a bit of an explanation of my formula. Do take care when lifting!!

IFERROR wrapper should be self-explanatory.
Google SUMPRODUCT if you are not aware of what it does but essentially, it sums the products of one or more arrays.
You can highlight any element of the formula, in the formula bar, and press F9 to evaluate that element as I have done to create the below.
Hopefully, it illustrates how each element of the formula is resolved. Firstly the array elements then the products and the sums.
Within the Sumproduct, any True False values become 1's or 0's when operated on by a math operator. * equates to AND and + equates to OR

Excel 2007
	D
1	Average
2	20.33333333
3	20.33333333
4	20.33333333
5	20.33333333
6	20.33333333
7	20.33333333
8	20.33333333
9	20.33333333
10	20.33333333
Sheet48

Cell Formulas
Range	Formula
D2	=IFERROR(SUMPRODUCT(((B2:B11="A")+(B2:B11="P"))*C2:C11)/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D3	=IFERROR(SUMPRODUCT(({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}+(B2:B11="P"))*C2:C11)/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D4	=IFERROR(SUMPRODUCT(((B2:B11="A")+{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE})*C2:C11)/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D5	=IFERROR(SUMPRODUCT(({0;0;0;0;1;1;1;0;0;0})*C2:C11)/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D6	=IFERROR(SUMPRODUCT(((B2:B11="A")+(B2:B11="P"))*{31;31;25;27;27;11;23;25;27;25})/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D7	=IFERROR(SUMPRODUCT({0;0;0;0;27;11;23;0;0;0})/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D8	=IFERROR(61/SUMPRODUCT((B2:B11="A")+(B2:B11="P")),"")
D9	=IFERROR(SUMPRODUCT(((B2:B11="A")+(B2:B11="P"))*C2:C11)/3,"")
D10	=IFERROR(61/3,"")

BTW I do charge by the kilogram!!

 

Last edited: Jun 11, 2014

Upvote 0

C

CLARE83

New Member

Joined

Jun 11, 2014

Messages

28

• Jun 11, 2014

• #9

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

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• Jun 11, 2014

• #10

Hi

Two other formulas ... for a wider choice

=SUMPRODUCT(SUMIF($B$2:$B$11,{"A";"P"},$C$2:$C$11))/SUMPRODUCT(--($B$2:$B$11={"A";"P"}))

and ENTER

or

=AVERAGE(INDEX(IF($B$2:$B$11<>"A",IF($B$2:$B$11="P",$C$2:$C$11),$C$2:$C$11),0))

and CTRL+SHIFT+ENTER

Regards

 

Upvote 0