Priority Formula

J

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
172
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
Hi, Is there a formula that can list the highest "Priority" based on Due date and Product as priority "1" being the highest?

As an example for Apple - the Earliest due date is 08/10/2020 so this will be Priority 1. Priority 2 would be 23/10/2020.

Thank you

Due DateProductQuantityPriority
30/10/2020Apple22,000.0
20/10/2020Orange4,800.0
15/10/2020Orange7,155.0
2/10/2020Pear3,800.0
8/10/2020Apple800.0
22/10/2020Orange1,750.0
26/10/2020Pear66,250.0
23/10/2020Apple1,060.0
12/10/2020Orange5,500.0
28/10/2020Apple3,500.0
27/10/2020Pear16,000.0
21/11/2020Banana13,000.0
11/11/2020Banana10,873.0
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
取数求助.xlsm
ABCD
1Due DateProductQuantityPriority
230/10/2020Apple22,000.002
320/10/2020Orange4,800.002
415/10/2020Orange7,155.003
52/10/2020Pear3,800.003
68/10/2020Apple8001
722/10/2020Orange1,750.001
826/10/2020Pear66,250.002
923/10/2020Apple1,060.004
1012/10/2020Orange5,500.004
1128/10/2020Apple3,500.003
1227/10/2020Pear16,000.001
1321/11/2020Banana13,000.001
1411/11/2020Banana10,873.002
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=SUM(--(A2<INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
hnsd24_CN said:
[RANGE = rs：14 | cs：4 | w：取数求助.xlsm | cls：xl2bb-128 | s：Sheet2 | tw：392] [XR] [XH] [/ XH] [XH = w：106] A [/ XH] [XH = w：90] B [/ XH] [XH = w：93] C [/ XH] [XH = w：103] D [/ XH] [/ XR] [XR] [XH ] 1 [/ XH] [XD = h：l | v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww]到期日[/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Product [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb bbw] Quantity [/ XD] [XD = h： l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Priority [/ XD] [/ XR] [XR] [XH] 2 [/ XH] [XD = v：m | bc：FEFEFE | ch ：17.25 | cls：bl bt br bb ww] 30/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Apple [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：22000] 22,000.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt bb bb ww] [FORMULA =' {= SUM（-（A2 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B2，ROW（$ 2：$ 14）-1）， 4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B2））））））））））））））））））））））））））））]] 2 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 3 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww] 20/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb bb ww] Orange [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：4800] 4,800.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（- -（A3 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B3，ROW（$ 2：$ 14）-1,4 ^ 8）， ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B3））））））））））））））））））））））））））））}）））}））}）}]] [] 2 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 4 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww] 15/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww]橙色[/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：7155] 7,155.00 [/ XD] [XD = v ：m | bc：FEFEFE | cls：fx bl bt br bb bb ww] [FORMULA ='{= SUM（-（A4 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF （$ B $ 2：$ B $ 14 = B4，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B4））））） ））））+ 1}'] 3 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 5 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls： bl bt br bb ww | tx：2/10/2020] 2/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Pear [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb bb ww | tx：3800] 3,800.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt bb bb ww] [FORMULA ='{= SUM（- -（A5 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B5，ROW（$ 2：$ 14）-1,4 ^ 8）， ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B5））））））））））））））））））））））））））））））））}）））}））}}] 3] [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 6 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww | tx：8/10/2020] 8/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Apple [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt bb bb ww] 800 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（-（A6 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1} ，SMALL（IF（$ B $ 2：$ B $ 14 = B6，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B6） ）））））））+ 1）'] 1 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 7 [/ XH] [XD = v：m | bc：FEFEFE | ch： 17.25 | cls：bl bt br bb ww] 22/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb bb ww]橙色[/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb bb ww | tx：1750] 1,750.00 [/ XD ] [XD = v：m | bc：FEFEFE | cls：fx bl bt bb bb ww] [FORMULA ='{= SUM（-（A7 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1 }，SMALL（IF（$ B $ 2：$ B $ 14 = B7，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B7 ）））））））））+ 1）'] 1 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 8 [/ XH] [XD = v：m | bc：FEFEFE | ch ：17.25 | cls：bl bt br bb ww] 26/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb bbw] Pear [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：66250] 66,250.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt bb bb ww] [FORMULA =' {= SUM（-（A8 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B8，ROW（$ 2：$ 14）-1）， 4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B8）））））））））））））））））））））））））}）]] 2 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 9 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb bb ww] 23/10/2020 [/ XD] [XD = h： l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Apple [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：1060] 1,060.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（-（A9 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B9，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B9））））））））））））））））}}） '] 4 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 10 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb bb ww | tx ：12/10/2020] 12/10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww]橙色[/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：5500] 5,500.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（ -（A10 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B10，ROW（$ 2：$ 14）-1,4 ^ 8）） ，ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B10）））））））））））））））））））））））））））））））））））}）））}）]] 4] [/ FORMULA] [/ XD] [/ XR] [XR ] [XH] 11 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww] 28/10/2020 [/ XD] [XD = h：l | v： m | bc：FEFEFE | cls：bl bt br bb ww] Apple [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：3500] 3,500.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（-（A11 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B11，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B11）））））））））））））））））+ 1） '] 3 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 12 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb ww] 27 / 10/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb bb ww] Pear [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：16000] 16,000.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb bb ww] [FORMULA ='{= SUM（-（A12 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B12，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1： $“＆COUNTIF（$ B $ 2：$ B $ 14，B12））））））））））））））））}）））}]] 1 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 13 [/ XH ] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb bb ww] 21/11/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Banana [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：13000] 13,000.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（-（A13 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B13，ROW（$ 2：$ 14）-1,4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B13））））））））））））））））+ 1） '] 1 [/ FORMULA] [/ XD] [/ XR] [XR] [XH] 14 [/ XH] [XD = v：m | bc：FEFEFE | ch：17.25 | cls：bl bt br bb bb ww | tx ：11/11/2020] 11/11/2020 [/ XD] [XD = h：l | v：m | bc：FEFEFE | cls：bl bt br bb ww] Banana [/ XD] [XD = v：m | bc：FEFEFE | cls：bl bt br bb ww | tx：10873] 10,873.00 [/ XD] [XD = v：m | bc：FEFEFE | cls：fx bl bt br bb ww] [FORMULA ='{= SUM（ -（A14 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B14，ROW（$ 2：$ 14）-1,4 ^ 8）） ，ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B14））））））））））））））））））））））））））}））））}））））}））]] [] 2 [/ FORMULA] [/ XD] [/ XR] [/ RANGE] [RANGE = cls：xl2bb-extra-128 | t：cf | f：xtra] [XR] [XD] D2：D14 [/ XD] [XD = fw：b] D2 [/ XD] [XD = c ：FF0000] = SUM（-（A2 <INDEX（$ A $ 2：$ A $ 14，N（IF（{1}，SMALL（IF（$ B $ 2：$ B $ 14 = B2，ROW（$ 2：$ 14）-1） ，4 ^ 8），ROW（INDIRECT（“ $ 1：$”＆COUNTIF（$ B $ 2：$ B $ 14，B2）））））））））））））））+ 1 [/ XD] [/ XR] [/ RANGE]
[/引用]
好像出了点问题
Click to expand...
 
Upvote 0
hnsd24_CN said:
取数求助.xlsm
ABCD
1Due DateProductQuantityPriority
230/10/2020Apple22,000.002
320/10/2020Orange4,800.002
415/10/2020Orange7,155.003
52/10/2020Pear3,800.003
68/10/2020Apple8001
722/10/2020Orange1,750.001
826/10/2020Pear66,250.002
923/10/2020Apple1,060.004
1012/10/2020Orange5,500.004
1128/10/2020Apple3,500.003
1227/10/2020Pear16,000.001
1321/11/2020Banana13,000.001
1411/11/2020Banana10,873.002
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=SUM(--(A2<INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
hnsd24_CN said:
取数求助.xlsm
ABCD
1Due DateProductQuantityPriority
230/10/2020Apple22,000.002
320/10/2020Orange4,800.002
415/10/2020Orange7,155.003
52/10/2020Pear3,800.003
68/10/2020Apple8001
722/10/2020Orange1,750.001
826/10/2020Pear66,250.002
923/10/2020Apple1,060.004
1012/10/2020Orange5,500.004
1128/10/2020Apple3,500.003
1227/10/2020Pear16,000.001
1321/11/2020Banana13,000.001
1411/11/2020Banana10,873.002
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=SUM(--(A2<INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...

Thanks hnsd24_CN. I have copied your formula but i'm getting a different result than you?
=SUM(--(A2<INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1


Due DateProductQuantityPriority
30/10/2020Apple22,000.01
20/10/2020Orange4,800.02
15/10/2020Orange7,155.03
2/10/2020Pear3,800.03
8/10/2020Apple800.04
22/10/2020Orange1,750.01
26/10/2020Pear66,250.02
23/10/2020Apple1,060.03
12/10/2020Orange5,500.04
28/10/2020Apple3,500.02
27/10/2020Pear16,000.01
21/11/2020Banana13,000.01
11/11/2020Banana10,873.02
 
Upvote 0
maybe
Due DateProductQuantityPriority
30/10/2020Apple220004
20/10/2020Orange48009
15/10/2020Orange71558
02/10/2020Pear380011
08/10/2020Apple8001
22/10/2020Orange175010
26/10/2020Pear6625012
23/10/2020Apple10602
12/10/2020Orange55007
28/10/2020Apple35003
27/10/2020Pear1600013
21/11/2020Banana130006
11/11/2020Banana108735
 
Upvote 0
Thanks everyone for your help.

I have entered the correct answer below for your reference. Note - The priority needs to be based on per product not as an overall. I hope this makes sense. Thank you

Due DateProductQuantityPriorityCorrect Answer
30/10/2020Apple22,000.04
20/10/2020Orange4,800.03
15/10/2020Orange7,155.02
2/10/2020Pear3,800.01
8/10/2020Apple800.01
22/10/2020Orange1,750.04
26/10/2020Pear66,250.02
23/10/2020Apple1,060.02
12/10/2020Orange5,500.01
28/10/2020Apple3,500.03
27/10/2020Pear16,000.03
21/11/2020Banana13,000.02
11/11/2020Banana10,873.01
 
Upvote 0
I've made some changes. Notice that column A is formatted as a date,
取数求助.xlsm
ABCD
1Due DateProductQuantityPriority
230/10/2020Apple22,000.004
320/10/2020Orange4,800.003
415/10/2020Orange7,155.002
502/10/2020Pear3,800.001
608/10/2020Apple8001
722/10/2020Orange1,750.004
826/10/2020Pear66,250.002
923/10/2020Apple1,060.002
1012/10/2020Orange5,500.001
1128/10/2020Apple3,500.003
1227/10/2020Pear16,000.003
1321/11/2020Banana13,000.002
1411/11/2020Banana10,873.001
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=SUM(--(A2>INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
hnsd24_CN said:
I've made some changes. Notice that column A is formatted as a date,
取数求助.xlsm
ABCD
1Due DateProductQuantityPriority
230/10/2020Apple22,000.004
320/10/2020Orange4,800.003
415/10/2020Orange7,155.002
502/10/2020Pear3,800.001
608/10/2020Apple8001
722/10/2020Orange1,750.004
826/10/2020Pear66,250.002
923/10/2020Apple1,060.002
1012/10/2020Orange5,500.001
1128/10/2020Apple3,500.003
1227/10/2020Pear16,000.003
1321/11/2020Banana13,000.002
1411/11/2020Banana10,873.001
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=SUM(--(A2>INDEX($A$2:$A$14,N(IF({1},SMALL(IF($B$2:$B$14=B2,ROW($2:$14)-1,4^8),ROW(INDIRECT("$1:$"&COUNTIF($B$2:$B$14,B2)))))))))+1
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
Thank you. Perfect. Thank you so much for your help.
 
Upvote 0
Johnboy28 said:
Thank you. Perfect. Thank you so much for your help.
Click to expand...

Sorry - Are you able to amend formula to suit added columns? Thank you. I cant seem to get it to work now?

Order #CustomerCustomer NameDue DateProductDescriptionQuantityTypeQty to FillPriority
30/10/2020Apple22,000.0
20/10/2020Orange4,800.0
15/10/2020Orange7,155.0
2/10/2020Pear3,800.0
8/10/2020Apple800.0
22/10/2020Orange1,750.0
26/10/2020Pear66,250.0
23/10/2020Apple1,060.0
12/10/2020Orange5,500.0
28/10/2020Apple3,500.0
27/10/2020Pear16,000.0
21/11/2020Banana13,000.0
11/11/2020Banana10,873.0
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula to work out the average value of the most recent entries
2
Replies
10
Views
1K
Jonny_71
J
K
  • Solved
Flagging Unique and Duplicate values via VBA
Replies
2
Views
592
Kiwihiker
K
L
  • Solved
Help with dynamic formula that will prints text
Replies
2
Views
171
Peter_SSs
Peter_SSs
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
483
fjns
F
B
  • Question
COUNTIF occurrences between two dates
Replies
1
Views
4K
jproffer
J

Forum statistics

Threads
1,215,008
Messages
6,122,672
Members
449,091
Latest member
peppernaut

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top