Ranking based on criteria

I need help to calculate ranking on the basis of criteria. Col. A has list of item Col. B has price for each item Col. C categorized each item as "urgent" & "Not Urgent". In col. D there is a date for each item. In col. E, i want to rank all "urgent" item on the basis of there Price and on the basis of the date in col.D which should be less than H2.
If the item has same price & is "urgent" then same rank should be given and also the ranking should be on continuous basis i.e., something like 1,2,2,3,4,5, and so on.
Kindly help to derive an excel formula for the same.

I hope you guys understood. My english is bad its not my first language thanks for the help in advance

I am using this formula in E4
=SUMPRODUCT(--(C4=$C$4:$C$23),--(B4<$B$4:$B$23))+1

However this takes the account of both "Urgent" & "Non Urgent" and also i am not able to think how bring the date criteria in it.

E4 cell formula , drag down

HTML Code: 

Thanks bebo021999 your formula works as i intended with a minor issue. I want to rank the "Urgent" items without skipping the ranking numbers. I have attached the worksheet with Col. E having your formula and deriving the rank. Whereas Col. F having my desired rank.
I am sure there must be some way to avoid the skipping of numbers in Rank.

Thanks for help

Revisit #1 requirement, it must include the date, the practice is the same as you, so your formula is correct

Originally Posted by FlameRetired

Hello andrei461. Welcome to the forum.

As far as the "Urgent"/"Non Urgent" ranking goes try array entering this formula in E4 and fill down If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=IFERROR(MATCH(IF(C4="Urgent",B4),LARGE(IF($C$4:$C$23="Urgent",$B$4:$B$23),ROW(INDIRECT("1:"&COUNTIF($C$4:$C$23,"Urgent")))),0),"")

As for the inclusion of date as criteria for rank I think we will need more information. Please attach another workbook to your next post with the hand typed results of what you expect with that criteria in mind. It might help us to understand better.

i have attached a sheet for your refernce. its a nice approach for using array entered formula. Ur formula takes into criteria of ranking "Urgent" item on the basis of decreasing price. I want to include a third criteria that will take account of Col. D values and check it with H2. If dates in col. D >= H2 then only i want to rank "Urgent" on the basis of decreasing "Price".

Thanks for your help

Hi andrei461 , you post at #8 , F7 should be 1 right ? but your result is blank
F4 cell formula

HTML Code: 

Originally Posted by Bo_Ry

Please try at E4

=IF((C4="Urgent")*(D4>H$2),MAX(1,SUM(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>$H$2)*(B$4:B$23>B4),B$4:B$23),B4))),"")

Thanks Bo_Ry for your response.

I tried using your formula but i guess its taking account of the maximum function and thus ranking ranking price of approx same value i.e., 3.5-3.44-3.3 with the same rank.

The sheet is attached for your reference.

Thanks for the help

Originally Posted by Bo_Ry

B8 should be number 3.44 not 3,44

14 May is more than 15 Apr
I add D$4:D$23<H$2+29 to check date with in a month

=IF((C4="Urgent")*(D4>=H$2)*(D4<H$2+29),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$H$2)*(D$4:D$23<H$2+29)*(B$4:B$23>B4),B$4:B$23),B$4:B$23)>0))+1,"")

Damn I am so sorry .. i was fast on replying i should have checked.

You are a life savor. Your formula is what i was looking for. I am gonna do some trials on the formula with different scenarios and if there is any issue i would come back or else i would change the status of the post to "Solved".

Thanks everyone for your responses.

I did not consider the problem. The correction is as follows. It has been clearly marked with colors. I feel that your simulated data still has problems. Please see my solution in column G. You try to change the D14 cell ,The date 2020/5/14 change to 2020/3/14. Pls See result

Originally Posted by wk9128

I did not consider the problem. The correction is as follows. It has been clearly marked with colors. I feel that your simulated data still has problems. Please see my solution in column G. You try to change the D14 cell ,The date 2020/5/14 change to 2020/3/14. Pls See result

Thanks wk9128, your solution works as charm for me
You and Bo_Ry has solved my query efficiently.

Thanks for your responses.