Average If function - Exclude Zero?

Question

I am using the following averageIf formula

=AVERAGEIF('Backend Data - Aerospace'!D:D, "Total",'Backend Data - Aerospace'!E:E)

However, it is counting 0's and i do not want this. Is there a way i can exclude 0's from being calculated as part of the average?

Thanks

Answer 1

To exclude zero values, you'd use the criteria <>0.

=AVERAGEIFS('Backend Data - Aerospace'!E:E,'Backend Data - Aerospace'!D:D, "Total",'Backend Data - Aerospace'!E:E,"<>0")

If you need a formula that doesn't ignore empty cells or if you're still using Excel 2003, you can use a formula in the following format:

=SUM(range)/COUNTIF(range,"<>0")