Average with multiple criteria in same and different column

Question

I have to find average number of days where Status = new or open, Severity = 4, and Area = Apps.

Demo table

Area     Severity    Status    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

The indicated rows meet the stated criteria, so the desired result is AVERAGE(20,12) which, of course, is 16.  I have tried =Average(IF((A:A="Apps")*(B:B="4")*(C:C="new")+(C:C="open"),D:D)) with Ctrl+Shift+Enter, but I am getting incorrect answer when using averageifs(); I am getting a #DIV/0! error. Please help me to fix this issue.

Answer 1

The fact that you have an "or" condition is going to screw up your use of AVERAGEIFS, since it only does "and" conditions. I think the best way to do this is to just calculate the average the old fashioned way:

=(SUMIFS(Days,Status,"new",Area,"Apps",Severity,4) +
SUMIFS(Days,Status,"open",Area,"Apps",Severity,4)) /
(COUNTIFS(Status,"new",Severity,4,Area,"Apps") +
COUNTIFS(Status,"open",Severity,4,Area,"Apps"))

I changed the ranges to named ranges for clarity, but you can replace those names with your particular ranges of cells.

Answer 2

You were close.  You need to respect the operator precedence rules and enclose the + subexpression in parentheses:

=AVERAGE(IF((A:A="Apps")*(B:B="4")*((C:C="new")+(C:C="open")), D:D))
                                   ↑                        ↑