Averageifs #DIV/0!

Averageifs #DIV/0!

I’m having the following issue with an averageifs formula.

I have thee averageifs formulas withing one formula (they have to add the three different column averages), however in some situations the criteria are not met.

For example I want to find the average of produce cardamom for town S. It is possible that in one of the column there is no cardamom for town S, but in the other columns there is. So I still want to have the average of the other 2 columns. Instead I get the #DIV/0! error.

What can be a solution to still get the average of the columns where the criteria are met?

See attachment for an example (also the average should only be calculated if the number is bigger than 0).

Thanks!

Try this in cell K2:

=AVERAGE(IF($A$2:$A$12=K$1,IF($B$2:$F$12=$J2,IF($C$2:$G$12>0,$C$2:$G$12))))

Enter with Ctrl+Shift+Enter.

Thanks! Does this work because it is not an averageif formula?

Thanks, I see. However my database is not like this. I'm trying to work with this database, because it will take a lot of time to remodel the whole database.