How to use Averageifs with empty cells

Question

I have a spreadsheet where in column A I have list of names and then in columns B to Z I have a number between 0 and 5. I would like to get the average of columns B to Z based on the names I select.

Name     Col 1     Col 2
Bob       4         2
John      3         5
Zed                 5

Here is what I tried:

The following produces me 3 if the name is Bob

=AVERAGEIF(A2:A4, "Bob", B2:D4)

However, if I change the name to Zed then I get can't divide by 0 because col 1 is not filled out. I would like to be able to have it only count if its a number in there. I thought of maybe putting a -1 in the column and have multiple conditions so that it counts if its > -1 but I can't seem to get the formula working. Here is what I tried:

This works fine:

=AVERAGEIFS( B2:D4, B2:D4, ">-1")

But when I tried to do combine with name I always get the same error, so I tried isolating it and I still get the same error.

=AVERAGEIFS( B2:D4, A2:A4, "=Bob")

The above produces the following error:

array arguments to averageifs are of different size

I don't quite get what I am doing wrong.

Also tried with a filter, not much luck there either:

=AVERAGEIF(A3:A43, "Bob", FILTER(B2:D4, NOT(ISBLANK(B2:B4))))

Answer 1

You can use ARRAYFORMULA/IF with AVERAGE:

=arrayformula(average(if((E1=A2:A4)*(B2:C4>0),B2:C4,"")))

Answer 2

You can also use a query

=IFERROR(AVERAGE(QUERY(A22:C,"where A='"&D21&"'")))

(Please adjust ranges to your needs)

Functions used:

• QUERY
• AVERAGE
• IFERROR

Answer 3

Another approach. This will produce a report of all names and averages:

=ArrayFormula({FILTER(A2:A,A2:A<>""),MMULT(FILTER(IF(B2:Z>0,B2:Z,0),A2:A<>""),SEQUENCE(COLUMNS(B:Z),1,1,0))/MMULT(FILTER(IF(B2:Z>1,1,0),A2:A<>""),SEQUENCE(COLUMNS(B:Z),1,1,0))})

Just be sure to place it outside of the range A:Z, or you'll get a circular dependency error. If you move this off to a different sheet, be sure to precede all ranges with the name of the source sheet.

This could have been shorter if I just hard-coded in the number of columns B:Z (i.e., 25), etc. Instead, it is written to be easy to edit if, for instance, you change your range to B:AA, etc.