Thread: summing and ranking a table in excel

I have a table with 4 columns City, Industry, Title and Amount
I need to sum the data by City i.e. total for Milan = 2200 and London = 1300 and then rank city based on total. Milan will be ranked 1 and London 2
I then need to sum and rank on Industry within City i.e. Milan and Telecommunication = 1700 and Milan and IT = 500 so Milan and Telecommunications will be ranked 1 and Milan and IT will be ranked 2
It needs to be further summed and ranked by Title within Industry within City:
Milan and IT and DBA = 500 and ranked 1
Milan and Telecommunication and System Administrator = 800 and ranked 1 (within the Milan and Telecommunications group)
Milan and Telecommunication and Call Centre Agent = 300 and ranked 3 (within the Milan and Telecommunications group)
Milan and Telecommunication and Legal Advisor = 600 and ranked 2 (within the Milan and Telecommunications group)

Please help me figure this one out. I have attached a sample of the table with data and expected results

Try this:

In H2:

=SUMIF($A$2:$A$9,A2,$D$2:$D$9)

copied down

In I2:

=SUMIFS($D$2:$D$9,$A$2:$A$9,A2,$B$2:$B$9,B2)

in J2:

=SUMIFS($D$2:$D$9,$A$2:$A$9,A2,$B$2:$B$9,B2,$C$2:$C$9,C2)

Those are the helper columns...

Now to get rankings...

in K2:

=SUMPRODUCT(--(COUNTIF(OFFSET(H$2,,,ROW(H$2:H$9)-ROW(H$2)+1),H$2:H$9)=1),--(H$2:H$9>H2))+1

copied down

in L2:

=SUMPRODUCT(--(COUNTIF(OFFSET(I$2,,,ROW(I$2:I$9)-ROW(I$2)+1),I$2:I$9)=1),--($A$2:$A$9=$A2),--(I$2:I$9>I2))+1

copied down

in M2:

=SUMPRODUCT(--(COUNTIF(OFFSET(J$2,,,ROW(J$2:J$9)-ROW(J$2)+1),J$2:J$9)=1),--($A$2:$A$9=$A2),--($B$2:$B$9=$B2),--(J$2:J$9>J2))+1

copied down.

I found one mismatch to your "expected data" at M6... not sure if it is mine or your error.

Does this fix it?

=SUMPRODUCT(--(COUNTIF(OFFSET(J$2,,,ROW(J$2:J$9)-ROW(J$2)+1),J$2:J$9)>=1),--($A$2:$A$9=$A2),--($B$2:$B$9=$B2),--(J$2:J$9>J2))+1

in M2, copied down.