Using AVERAGEIF with a range spanning multiple columns

Hello,
I have been able to successfully use AVERAGEIF to find the average of values in a column. Can I also use it to get the average of values in a table? When I try to do this, the average is not correct when I compare to manual calculation.

I have tried this with and without naming the range, but the result still seems to be the same.

I'll attach a document with my current work.

- Averages Section (this is the part that I am using formulas to calculate the averages of the data entry down below). The problem area is in RED. I want to get an average of all the scores for a product, across all features/columns in B15-0213 (named range is "scores"). I have to do this periodically, so I would like to set up the formulas so they need no alteration in the future.

- Scoring Section (this is where the scores are entered individually) I put this into a table, so that it would be easier to name the ranges, and items could be filtered.

- Product Filter- you can use this to filter the scores for a certain product, then highlight over all the scores to see an average to verify the formulas above.

The quick answer is that you could use array formulas.

P3:

Formula:

Please Login or Register  to view this content.

Your P3 formula, =AVERAGEIF(Table13[Product],$A3,Scores), returns the same result as =AVERAGEIF(Table13[Product],$A3,INDEX(Scores,0,1)), so it appears AVERAGEIF constrains its 3rd argument to the same size as its 1st argument. FWIW, =AVERAGEIFS(Scores,Table13[Product],$A3) returns #VALUE! . This isn't how I recall it working in previous Excel versions, but I don't have any others with which to test at the moment, only Office 365.

Usually I'd suggest SUMPRODUCT instead, but in this case you also have text N/A values in Scores, which scuppers a single SUMPRODUCT call, though you could use

P3:

Formula:

Please Login or Register  to view this content.

however, the array formula above is more efficient.