AverageIF question

[kong1802]

I have a large set of data. I only want the average of two cells from two separate columns. I'm trying to just get the average of the last two years, by month, of utility expenses. My issue is that I do not want the average if one of the months was a zero. The averageif function seems to want a range. When I try to separate showing that I only want the two cells, the formula will not work. Below is what I'm trying, and it obviously will not work. How do you write the formula to just look at two cells, and not a range?

=AVERAGEif('[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS4,'[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!BE4,>0)

 

[kong1802]

Ok, I figured out a work around. I just did the following:

=('[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS4+'[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!BE4)/(('[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS4<>0)+('[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!BE4<>0))

 

[Jonmo1]

You may have to do

=SUM(Cell1, Cell2)/((Cell1<>0)+(Cell2<>0))

Or is there any way to look at another row of cells to determine which cells to include in the average?
Like a Header row, and only include cells which say "total" (or whatever) in row 3, and of coarse only cells that are not 0.
Then we can use AverageIFS

=AVERAGEIFS('[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS4:BE4,'[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS$3:BE$3,"Total",'[Utilities PD 09-2017 (G Strickler).xlsb]Overview'!AS4:BE4,"<>0")