rank formula with multiple conditions

D

DerekWooley

New Member
Joined
May 1, 2018
Messages
34
Hello, I am trying to create a formula to rank product run-out dates of items with multiple conditions such as material type, size, etc.

My sheet looks like below. I want to prioritize by run-out out date and then group material, then by size. I thought about grouping the run-out dates by quarter and month to make the run-out dates easier to rank using the following formula but it just ranks the months for each quarter instead of ranking the quarters as well to see which items should come first.

SUMPRODUCT(--(E2=$E$2:$E$20),--(F2>$F$2:$F$20))+1

[TABLE="width: 397"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 474"]
<colgroup><col><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 415"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 445"]
<colgroup><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]Runout Date[/TD]
[TD] [/TD]
[TD]Material[/TD]
[TD]Size[/TD]
[TD] [/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD="align: right"]99364[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD] [/TD]
[TD]Copper[/TD]
[TD="align: right"]0.25[/TD]
[TD] [/TD]
[TD]20192[/TD]
[TD]201904[/TD]
[/TR]
[TR]
[TD="align: right"]4543[/TD]
[TD="align: right"]11/30/2018[/TD]
[TD] [/TD]
[TD]Plastic[/TD]
[TD="align: right"]0.5[/TD]
[TD] [/TD]
[TD]20184[/TD]
[TD]201811[/TD]
[/TR]
[TR]
[TD="align: right"]5435[/TD]
[TD="align: right"]1/22/2019[/TD]
[TD] [/TD]
[TD]Plastic[/TD]
[TD="align: right"]0.45[/TD]
[TD] [/TD]
[TD]20191[/TD]
[TD]201901[/TD]
[/TR]
[TR]
[TD="align: right"]4564[/TD]
[TD="align: right"]5/26/2018[/TD]
[TD] [/TD]
[TD]Plastic[/TD]
[TD="align: right"]0.5[/TD]
[TD] [/TD]
[TD]20182[/TD]
[TD]201805[/TD]
[/TR]
[TR]
[TD="align: right"]23335[/TD]
[TD="align: right"]7/18/2018[/TD]
[TD] [/TD]
[TD]Aluminum[/TD]
[TD="align: right"]0.5[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201807[/TD]
[/TR]
[TR]
[TD="align: right"]6544[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD] [/TD]
[TD]Copper[/TD]
[TD="align: right"]0.75[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201808[/TD]
[/TR]
[TR]
[TD="align: right"]34463[/TD]
[TD="align: right"]9/17/2018[/TD]
[TD] [/TD]
[TD]Copper[/TD]
[TD="align: right"]0.45[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201809[/TD]
[/TR]
[TR]
[TD="align: right"]4544[/TD]
[TD="align: right"]9/12/2018[/TD]
[TD] [/TD]
[TD]Copper[/TD]
[TD="align: right"]0.25[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201809[/TD]
[/TR]
[TR]
[TD="align: right"]654[/TD]
[TD="align: right"]7/14/2018[/TD]
[TD] [/TD]
[TD]Aluminum[/TD]
[TD="align: right"]0.75[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201807[/TD]
[/TR]
[TR]
[TD="align: right"]4544[/TD]
[TD="align: right"]9/2/2018[/TD]
[TD] [/TD]
[TD]Aluminum[/TD]
[TD="align: right"]0.25[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201809[/TD]
[/TR]
[TR]
[TD="align: right"]4345[/TD]
[TD="align: right"]8/20/2018[/TD]
[TD] [/TD]
[TD]Steel[/TD]
[TD="align: right"]0.75[/TD]
[TD] [/TD]
[TD]20183[/TD]
[TD]201808[/TD]
[/TR]
[TR]
[TD="align: right"]77788[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD] [/TD]
[TD]Steel[/TD]
[TD="align: right"]0.25[/TD]
[TD] [/TD]
[TD]20184[/TD]
[TD]201810[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I can provide more detail if that makes it easier about what I am trying to do.[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Would appreciate any help.

Thank you,
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=COUNTIF(<font color="Blue">B$2:B$13,VLOOKUP(<font color="Red">K$3,$K$6:$L$7,2,0</font>)&B2</font>)+COUNTIFS(<font color="Blue">B$2:B$13,B2,$C$2:$C$13,VLOOKUP(<font color="Red">$L$3,$K$6:$L$7,2,0</font>)&C2</font>)+COUNTIFS(<font color="Blue">B$2:B$13,B2,$C$2:$C$13,C2,$D$2:$D$13,VLOOKUP(<font color="Red">M$3,$K$6:$L$7,2,0</font>)&D2</font>)+1</td></tr></tbody></table></td></tr></table><br />





Excel 2013/2016
ABCDEFGHIJKLM
1ItemRunout DateMaterialSizeQuarterMonth
245645/26/2018Plastic0.5201822018052Runout DateMaterialSize
36545/26/2018Aluminum0.75201832018071rank typeAscendingAscendingDescending
4233357/18/2018Aluminum0.5201832018073
565448/15/2018Copper0.75201832018084
643458/15/2018Steel0.75201832018085Ascending<
745449/2/2018Aluminum0.25201832018096Descending>
845449/12/2018Copper0.25201832018097
9344639/17/2018Copper0.45201832018098
107778810/1/2018Steel0.25201842018109
11454311/30/2018Plastic0.52018420181110
1254351/22/2019Plastic0.452019120190111
13993644/6/2019Copper0.252019220190412
Sheet3
 
Upvote 0
Thank you very much. Is there a way to do this in excel 2003? The countifs function doesn't seem to work for me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Minimum of the multiple dates for a Product
2
Replies
10
Views
2K
Fluff
Fluff
M
  • Question Question
Vba to distribute days across multiple years
Replies
5
Views
1K
motherindia
M
D
  • Locked
  • Question Question
excel 2003 Countif 3 to 4 criteria
Replies
1
Views
1K
Peter_SSs
Peter_SSs
D
  • Question Question
excel 2003 Countifs
Replies
3
Views
1K
Oscar Cronquist
O
J
  • Question Question
Formula to Determine REPEAT Offenders
Replies
9
Views
7K
JustBrowsing305
J

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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