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

#### M_Shweta

##### New Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

##### MrExcel MVP
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

##### Well-known Member
 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

##### New Member
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

##### MrExcel MVP

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:
Rich (BB code):
``````=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

##### New Member
 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

##### MrExcel MVP

Sir the answer should be 16

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

#### M_Shweta

##### New Member
hehe its done thanks thanks alot

##### MrExcel MVP

Replies
1
Views
174
Replies
5
Views
469
Replies
3
Views
1K
Replies
8
Views
1K
Replies
1
Views
103

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,194
Messages
5,768,778
Members
425,495
Latest member
Ragamacam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back