Averageif formula over several columns to exclude numbers based on adjoining cell

[Kimber]

I have a worksheet with multiple percentages and coordinating description columns. On worksheets with a set of columns, I can use the formula =AVERAGEIFS(C:C,D:D,"<>Not needed"). However, I need to calculate multiple columns -- shown on the attached worksheet with the same status exclusion. I believe it would require using AND but I am stuck. Thank you.

 

[Peter Bartholomew]

= AVERAGEIFS(percentage,status,"<>Not needed")

Note the overlapping ranges. The formula will try to average the 'status' columns as well as the 'percentage' but, being text, they contribute nothing to the average.

 

[pecoflyer]

One out of two columns having identical names might put you in trouble for future analysis

 

[Peter Bartholomew]

The solution I proposed uses named ranges that incorporate all columns of the table, and operates over 2D ranges.

 

[Kimber]

= AVERAGEIFS(percentage,status,"<>Not needed")

Note the overlapping ranges. The formula will try to average the 'status' columns as well as the 'percentage' but, being text, they contribute nothing to the average.


View attachment 76001

Thank you for a streamlined, non-complicated approach that makes sense and works perfectly, Peter.

 

[Kimber]

One out of two columns having identical names might put you in trouble for future analysis

Agreed but using named ranges solves that problem.