Average of multiple values in same column range and in different columns range

[M_Shweta]

I have to find average number of days if status = new or open,severity= 4,area=Apps

Demo table
Area Severity Status No of days

Apps 4 new 20
Apps 3 open 10
Node 3 close 0
Device 2 new 25
Apps 4 open 12
Device 1 close 0
Node 4 new 13


I have tried =Average(if((A:A="Apps")*(B:B="4")*(C:C="new")+(C:C="open"),D:D)) cntrl+shft+enter but i am getting incorrect answer when using averageifs () i am getting div/0 error.Please help me to fix this issue.Thank you

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(A2:A8="Apps",IF(B2:B8=4,IF(ISNUMBER(MATCH(C2:C8,{"new";"open"},0)),D2:D8))))

 

[oldbrewer]

Apps	4	new	20
Apps	3	open	10
Node	3	close	0
Device	2	new	25
Apps	4	open	12
Device	1	close	0
Node	4	new	13
		32
		=SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="new")*(D1:D7))+SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="open")*(D1:D7))

<colgroup><col><col><col><col><col span="11"></colgroup><tbody>
</tbody>

 

[M_Shweta]

Control+shift+enter, not just enter:

=AVERAGE(IF(A2:A8="Apps",IF(B2:B8=4,IF(ISNUMBER(MATCH(C2:C8,{"new";"open"},0)),D2:D8))))

Thanks for the quick response but again i got Div/0 error

 

[Aladin Akyurek]

Thanks for the quick response but again i got Div/0 error

How did you manage that?

Area	Severity	Status	No of days		Avg
Apps	4	new	20		16
Apps	3	open	10
Node	3	close	0
Device	2	new	25
Apps	4	open	12
Device	1	close	0
Node	4	new	13

<COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2560" width=72><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3811" width=107><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

F2:

=AVERAGE(IF(A2:A8="Apps",IF(B2:B8=4,IF(ISNUMBER(MATCH(C2:C8,{"new";"open"},0)),D2:D8))))

which you must confirm by pressing down the control and the shift keys at the same while you hit enter.

See the workbook that implements this formula:
https://dl.dropboxusercontent.com/u/65698317/M_Shweta%20multiconditional%20average.xlsx

 

[M_Shweta]

Apps	4	new	20
Apps	3	open	10
Node	3	close	0
Device	2	new	25
Apps	4	open	12
Device	1	close	0
Node	4	new	13
		32
		=SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="new")*(D1:D7))+SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="open")*(D1:D7))

<tbody>
</tbody>

Sir the answer should be 16

 

[Aladin Akyurek]

Sir the answer should be 16

Post #5 advertises a workbook showing 16 as the average...

 

[M_Shweta]

hehe its done thanks thanks alot

 

[Aladin Akyurek]

hehe its done thanks thanks alot

You are welcome.