I've done this on a single sheet but you should be able to adapt to another.
My actual results don't make a lot of sense as I don't have sufficient sample data but hopefully it will put you on the right track.
If you have the latest version of excel you could use the formulas in columns G:I
If Excel 2010 or later columns M:O
If older, post back with your version.
HLA
| G | H | I | J | K | L | M | N | O |
2 | 4.5 | 4.5 | 4.5 | | Item A | A | 4.5 | 4.5 | 4.5 |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:55px;"><col style="width:82px;"><col style="width:14px;"><col style="width:57px;"><col style="width:159px;"><col style="width:58px;"><col style="width:58px;"><col style="width:82px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas |
Cell | Formula | G2 | =MAXIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) | H2 | =MINIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) | I2 | =AVERAGEIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) | M2 | =AGGREGATE(14,6,B$2:B$10/((A$2:A$10=K2)*(C$2:C$10=L2)),1) | N2 | =AGGREGATE(15,6,B$2:B$10/((A$2:A$10=K2)*(C$2:C$10=L2)),1) | O2 | =SUMIFS(B$2:B$10,A$2:A$10,K2,C$2:C$10,L2)/COUNTIFS(A$2:A$10,K2,C$2:C$10,L2) |
<tbody>
</tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4