Sumproduct to get averages and ranks

[Yvonne Love]

Although I am just now scratching the surface of Sumproduct, I think it can accomplish what I am trying to do but I haven't been able to find any examples so I'm looking for some help.

I posted in the last couple of weeks the need to get the average per company by region from a table that has multiple instances of the company, and someone provided a sumproduct formula, which works. I now need to figure out how to modify it to factor in the year - so it will be the average per company by region by year - again, multiple instances of the company and the year.

I also need to find out if there is a way to rank using a sumproduct (or other) formula for a specific company by year in the same kind of table (multiple instances of company and year).

I am working through Chandoo's sumproduct masterclass now, but it hasn't addressed this scenario yet and I haven't been able to adequately or successfully search for this.

Attached is a spreadsheet with sample data. Any help would be greatly appreciated!

Thanks!
YL

 

[Hui]

I would be using Averageifs() function

You can have as many criteria as you need:
=AVERAGEIFS(Table1[Fees],Table1[Company],"AAA",Table1[Year],2016)

For ranking you need a list of Unique Company Numbers, then use the Rank Function

 

[Hui]

See attached file:

 

[Nebu]

Hi:

Use the following array formula to add additional criteria.

=SUMIFS(Sheet1!$F$2:$F$239,Sheet1!$B$2:$B$239,"Region 09",Sheet1!$E$2:$E$239,2016)/SUMPRODUCT((IFERROR(1/COUNTIFS(Sheet1!$A$2:$A$239,Sheet1!$A$2:$A$239,$E$2:$E$239,$E$2:$E$239),0))*(Sheet1!$B$2:$B$239="Region 09")*(E2:E239=2016))

Thanks