INDEX, MATCH, LARGE and/or SUMIF/SUMPRODUCT

[bsquad]

I am looking for a formula that will assist in the last two columns


-the first two columns is the raw data
-the third and fourth columns is a distinct for the class name and a sumproduct
-the last two is an Index/match/large

I am looking for an all in one formula and haven't gotten any. I have also searched all over the place and cannot seem to find such a combination.


--SEE SPREADSHEET ATTACHED FOR AN ORGANIZED VERSION




Class Count
Class 1 4 Class 1 6 Class 10 987
Class 2 3 Class 2 836 Class 2 836
Class 3 65 Class 3 83 Class 4 285
Class 4 2 Class 4 285 Class 3 83
Class 5 13 Class 5 13 Class 13 44
Class 3 3 Class 6 36 Class 12 41
Class 6 11 Class 7 30 Class 6 36
Class 7 23 Class 8 2 Class 7 30
Class 3 15 Class 9 12 Class 15 18
Class 4 3 Class 10 987 Class 5 13
Class 7 7 Class 11 1 Class 9 12
Class 4 5 Class 12 41 Class 9 12
Class 2 100 Class 13 44 Class 14 11
Class 8 2 Class 14 11 Class 1 6
Class 4 8 Class 15 18 Class 18 4
Class 4 267 Class 16 12 Class 17 3
Class 9 12 Class 17 3 Class 8 2
Class 10 800 Class 18 4 Class 8 2
Class 11 1 Class 19 2 Class 11 1
Class 10 15
Class 2 722
Class 10 54
Class 1 2
Class 10
Class 10 13
Class 12 41
Class 10 42
Class 10 17
Class 13 17
Class 10 3
Class 10 12
Class 14 11
Class 15 6
Class 10 17
Class 10 11
Class 16 12
Class 17 3
Class 10 3
Class 2 11
Class 18 4
Class 6 11
Class 6 11
Class 13 27
Class 6 3
Class 19 2
Class 15 12

[JBeaucaire]

Excel is a much more robust tool than some core functions. To do what you're doing with FORMULAS, you're doing it the correct way, with additional tables.

To do it efficiently in a single step, you would use a Pivot Table.
http://screencast.com/t/KyLEwR7BAk1