Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Sumproduct or Rank
I've this formula =SUMPRODUCT(($L$2:$L$35=L4)*(AO4<$AO$2:$AO$35))+1
1. It give me the correct ranking but from highest to lowest. I need them to be lowest to the highest. 2. At the moment it gives me the ranking based on column L. If I need it to give me the ranking based on 2 criteria (ie same country and same postal city). Country= column C, Postal City = column L. This is because the same postal city could be in two country. Ie Birmingham in UK and in US. Thanks. Kim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Sumproduct or Rank
going from highest to lowest is simply changing the "<" to "".
Here is the results for two cities =SUMPRODUCT(($C$2:$C$35=C4)*($L$2:$L$35=L4)*(AO4$ AO$2:$AO$35))+1 "Kim" wrote: I've this formula =SUMPRODUCT(($L$2:$L$35=L4)*(AO4<$AO$2:$AO$35))+1 1. It give me the correct ranking but from highest to lowest. I need them to be lowest to the highest. 2. At the moment it gives me the ranking based on column L. If I need it to give me the ranking based on 2 criteria (ie same country and same postal city). Country= column C, Postal City = column L. This is because the same postal city could be in two country. Ie Birmingham in UK and in US. Thanks. Kim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank If - SumProduct - HELP PLEASE | Excel Worksheet Functions | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Sumproduct if rank <=5 if not return 0 | Excel Discussion (Misc queries) | |||
Combining SUMPRODUCT and RANK functions | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |