• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Kimber

Member
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.
 

Attachments

  • 20210823 Average Multiple columns with exclusions.xlsx
    10.2 KB · Views: 3
Code:
= 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.


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

Attachments

  • 20210823 Average Multiple columns with exclusions.xlsx
    10.4 KB · Views: 2
Back
Top