AVERAGEIFS function - ignore blanks and add qualifying "if" statement

[clairestill]

I have this formula that works to be a nonblank average of a column J that matches to a specific answer in column B (A21). Column B has non-numerical values and Column J is the data I'm averaging.

=AVERAGEIF(B3:B20,A3,J3:J20)

When I try to expand this to eliminate "0's" from the averages, it messes up. How to I expand the formula above to only include non-zero answers?

=AVERAGEIFS(...)

 

[Domenic]

Assuming that Column J does not contain negative numbers, try...

=AVERAGEIFS(J3:J20,B3:B20,A3,J3:J20,">0")

Hope this helps!

 

[clairestill]

Assuming that Column J does not contain negative numbers, try...

=AVERAGEIFS(J3:J20,B3:B20,A3,J3:J20,">0")

Hope this helps!

Thank you so much for your prompt response!

I'm getting a "#DIV/0!" error response. There are no negative values, but there are blank cells. Can blanks cause an issue? (I simplified the version of the formula I put in here, it includes hundreds of cells)

 

[Domenic]

The formula will return #DIV/0! if there are no rows that meet the criteria. If there are indeed rows that meet the criteria, the numbers in Column J are likely being recognized as text values instead of numerical values. These numbers formatted as text values can easily be converted into numerical values as follows...

1) select an empty cell

2) click on Copy

3) select the range of cells in Column J containing the data or simply select the whole column

4) select PasteSpecial > Add > OK

Does this help?

 

[clairestill]

The formula will return #DIV/0! if there are no rows that meet the criteria. If there are indeed rows that meet the criteria, the numbers in Column J are likely being recognized as text values instead of numerical values. These numbers formatted as text values can easily be converted into numerical values as follows...

1) select an empty cell

2) click on Copy

3) select the range of cells in Column J containing the data or simply select the whole column

4) select PasteSpecial > Add > OK

Does this help?

YES, that completely helped. I was making the formula in a cell referencing an item that is not currently listed in the data set. However when I pulled the formula down to a cell with currently entered reference data it worked! (if that makes sense)

Thank you SO MUCH.

 

[Domenic]

That's great, glad you've got it sorted out.

Cheers!