Averageifs produces DIV/O, but average if does not?

[anonymous8472]

Calculating months on market..


When I just average if "building type" than I get accurate numbers


When I averageifs "submarket" and "building type" I get DIV/O error..


Why is this?

 

[Luke M]

When using an AVERAGEIFS, the function considers all the criteria as being "and" type statements. As nothing can be BOTH submarket and building type, this results in no values being fed into the function, and you get 0/0, causing an error.

 

[anonymous8472]

Ah...makes sense...thanks muchly..

 

[Luke M]

No problem. One way to get around would be to add up some SUMIF and COUNTIF functions and divide, like:

=(SUMIF(Range1,Crit1,Range2)+SUMIF(Range1,Crit2,Range2))/(COUNTIF(Range1,Crit1)+COUNTIF(Range1,Crit2))

 

[anonymous8472]

I was thinking something similar....I'll try it out and get back if it doesn't work...