Why is my AVERAGEIF returning #DIV/0!?

There could be an extra space hiding before or after the text. Excel treats “North ” with a space as different from “North” without one, even though they may look almost the same in the grid.

A value can look like a number while it is actually stored as text. AVERAGEIF only averages real numbers, so rows that pass the test can still leave nothing to average.