rank formula with multiple conditions

[DerekWooley]

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

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

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[VBA Geek]

<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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item	Runout Date	Material	Size	Quarter	Month
2	4564	5/26/2018	Plastic	0.5	20182	201805	2				Runout Date	Material	Size
3	654	5/26/2018	Aluminum	0.75	20183	201807	1			rank type	Ascending	Ascending	Descending
4	23335	7/18/2018	Aluminum	0.5	20183	201807	3
5	6544	8/15/2018	Copper	0.75	20183	201808	4
6	4345	8/15/2018	Steel	0.75	20183	201808	5				Ascending	<
7	4544	9/2/2018	Aluminum	0.25	20183	201809	6				Descending	>
8	4544	9/12/2018	Copper	0.25	20183	201809	7
9	34463	9/17/2018	Copper	0.45	20183	201809	8
10	77788	10/1/2018	Steel	0.25	20184	201810	9
11	4543	11/30/2018	Plastic	0.5	20184	201811	10
12	5435	1/22/2019	Plastic	0.45	20191	201901	11
13	99364	4/6/2019	Copper	0.25	20192	201904	12
Sheet3

 

[DerekWooley]

Thank you very much. Is there a way to do this in excel 2003? The countifs function doesn't seem to work for me.