Excel 2007 : Problem - Rank multiple criteria using SUMPRODUCT

Profile implies XL2010 - in which case you should use COUNTIFS

Please Login or Register  to view this content.

that said even with 5000 rows of data the above will be inefficient.

Given the above, if you can, (assuming XL2010) sort your data by Cols B, D, E & C (desc) at which point your formula becomes very basic / efficient.

Originally Posted by DonkeyOte

Profile implies XL2010 - in which case you should use COUNTIFS

Please Login or Register  to view this content.

that said even with 5000 rows of data the above will be inefficient.

Given the above, if you can, (assuming XL2010) sort your data by Cols B, D, E & C (desc) at which point your formula becomes very basic / efficient.

Yes (i do have excel 2010), That would be the best and easiest way to do it. The problem is, is that I need to have this huge file sorted by another criteria (5000 columns... rows to **) .

Lets assume, that on this example that for some reason, it's important to have it sorted by tenure (which we will pretend is F.) Thus, the solution (although is the best way for most) won't work

for my file, I've done the whole rankifs manually:
---I created, on a seperate sheet a rankifs (for the attached file, this is the wrong rankifs via sumproduct by the way) (=SUMPRODUCT(--(C3<($B$3:$B$8=B3)*$C$3:$C$8))+1) column for each separate criteria rank I wanted

--- then I wrote this formula as a means to a true/false (0 or 1) file (=IF(e3=day,0,(COUNTIFS($b3,"north",$B3,CT$1,$d3,"desk")))) (where (in the attached file's data) ct1 would equal which office)

--- then I have a third formula look at the second set of data, and if a 1 value (true) is in a countif row, then it will return the value of the correct Rankif row (via sumproduct)

Problems with my rankifs solution
huge file
Large amounts of new rows required for sorts (it's more like 28 offices, 10 jobs, 8 shifts) so you can see, since I have so many of these rankifs in play, the solution requires (for 2 different sorts) 112 rows

-----This is where the rankifs using sumproducts with 3 criteria (if possible)would come in uber-handy----

heh, sorry guys, most likely, I did a poorjob of explaining myself l in this post, I apologize if my bad sense of logic makes your head spin

The formula provided previously is for unsorted data.
Using your sample as provided paste the COUNTIFS formula from my prior post into F2 and copy down for rank values.

I simply made the point that sorting invariably allows for greater efficiencies.
If you can't sort so be it but be aware that 5000 COUNTIFS won't be that quick, however, they will still be significantly quicker than 5000 SUMPRODUCTs