# Rank if

#### Dan71

##### New Member
I was tring to remember how to do a "rank if". Finaly remembered how to do it using;

=sumproduct(--(crittera 1),--(critera 2),(sale<sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

Secondly, I tried firstly to acheve the same result using an array forula ie
{=rank(if(critera,sale,sales))} but could not get it to work. i did try a few diferent combinations of brackets etc. Is it posible to do it this way?

Thanks
Dan

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
don't manually type the brackets...

after typing the formula, press CTRL + SHIFT + ENTER. That put's the brackets around the formula.

=sumproduct(--(crittera 1),--(critera 2),(sale<sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

just swap the < for a > i.e.

=sumproduct(--(criteria 1),--(criteria 2),--(sale>sales))+1

I was tring to remember how to do a "rank if". Finaly remembered how to do it using;

=sumproduct(--(crittera 1),--(critera 2),(sale < sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

Secondly, I tried firstly to acheve the same result using an array forula ie
{=rank(if(critera,sale,sales))} but could not get it to work. i did try a few diferent combinations of brackets etc. Is it posible to do it this way?

Thanks
Dan

=SUMPRODUCT(--(RangeX=X),--(RangeY=Y),--(RankRange > Z))+1

Thanks for your help, Barry, that was spot on.

The part about the array formula was more for interest realy, I know how to use them ie use ctrl+shift+enter. I realy want to know if is posible to do a rank if in this way. I suppose the same would aplly to a "vlookup if" too. I see how "average ifs" etc work, it is formula with multiple parts to them like a vlookup, rank, etc ( in effect excell's built in array formulie).

Dan

Replies
4
Views
458
Replies
1
Views
309
Replies
11
Views
681
Replies
1
Views
288
Replies
5
Views
362

1,221,209
Messages
6,158,547
Members
451,498
Latest member
tyshanklin1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back