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

Averageifs on multiple criteria

I haven't been able to find an answer to that, but admittedly I may not be describing it correctly. And I feel like it should be a really obvious, easy answer that I am just overlooking.

I have a dummy spreadsheet attached showing a column for Company, Region, and Fees. Each client has more than one row, so using averageifs based on Region is not pulling the correct average if needing the average per company per region. The average should be the total fees for each company in each region divided by the # of companies in each region.

I assume some kind of array formula (or even something far easier) should be able to accomplish this, but I just can't find that formula. Am I overlooking something very simple? And if not, is there an array formula that will give me total fees for each company in a region (when a company has multiple rows) divided by unique companies in that region?

I've attached a dummy data spreadsheet for reference.

Thanks!
YL
 

Attachments

  • Dummy data for averageifs question.xlsx
    205.8 KB · Views: 4
  • Dummy data for averageifs question.xlsx
    205.8 KB · Views: 0
  • Dummy data for averageifs question.xlsx
    205.8 KB · Views: 1
Something like below will work.
=SUMIF(Table1[Region],"NORTH",Table1[Fees])/SUMPRODUCT((IFERROR(1/COUNTIF(Table1[Company],Table1[Company]),0))*(Table1[Region]="NORTH"))

Confirmed as array (CSE)
 
Back
Top