excel 2003 Countifs

[DerekWooley]

Hello, I am trying to find a way to rank the items in the below table by quarter from earliest to latest and then by month from earliest to latest after quarter in excel 2003. When I use the below sumproduct formula, it only ranks the months for each quarter but doesn't rank the quarters themselves. What do I need to add to rank quarter first, then month? Also, I want to see if it is possible to add other criteria like item type, material type, etc as secondary criteria to group items by.



Here is the sum product formula I am using: SUMPRODUCT(--(E3=$E$3:$E$46),--(F3>$F$3:$F$46))+1
I am trying to do a rank to get around sorting by multiple fields and excel 2003 only has 3 max sort criteria.​

I also want to know if it is possible to get a smoothed ranking from 1 to the end of my data instead of starting over each time it hits a new criteria; so instead of having to sort on multiple columns, I could just sort on one column or use a pivot table with the ranking first to pull in the prioritized items.

Below is what my table looks like.​

A	B	C	D	E	F	G	H	I
Item	Actual Date	Quarter Text	Month Text	Quarter Value	Month Value	Material	Size	Sum Product Ranking
970	8/4/2019	20193	20198	4/14/1955	4/19/1955	Brass	0.21875	1
254	12/2/2018	20184	201812	4/5/1955	7/15/2452	Brass	0.09375	4
345	1/24/2019	20191	20191	4/12/1955	4/12/1955	Brass	0.09375	6
883	5/28/2018	20182	20185	4/3/1955	4/6/1955	Steel	0.25	1
988	7/20/2018	20183	20187	4/4/1955	4/8/1955	Wood	0.25	1
302	8/17/2018	20183	20188	4/4/1955	4/9/1955	Plastic	0.25	1
94	8/11/2018	20183	20188	4/4/1955	4/9/1955	Brass	0.0625	2
242	11/26/2018	20184	201811	4/5/1955	7/14/2452	Brass	0.0625	2
280	11/8/2018	20184	201811	4/5/1955	7/14/2452	Brass	0.21875	3
606	11/10/2018	20184	201811	4/5/1955	7/14/2452	Brass	0.21875	3
347	10/8/2018	20184	201810	4/5/1955	7/13/2452	Plastic	0.1875	3
965	10/18/2018	20184	201810	4/5/1955	7/13/2452	Plastic	0.1875	1
479	4/6/2019	20192	20194	4/13/1955	4/15/1955	Poly	0.125	1
313	5/3/2019	20192	20195	4/13/1955	4/16/1955	Poly	0.125	1
354	10/20/2019	20194	201910	4/15/1955	10/21/2452	Brass	0.21875	3
419	6/8/2020	20202	20206	4/23/1955	4/27/1955	Brass	0.09375	1
201	7/12/2019	20193	20197	4/14/1955	4/18/1955	Brass	0.09375	3
960	1/7/2019	20191	20191	4/12/1955	4/12/1955	Wood	0.25	1
593	2/5/2019	20191	20192	4/12/1955	4/13/1955	Plastic	0.25	1
224	5/2/2020	20202	20205	4/23/1955	4/26/1955	Brass	0.21875	3
114	5/10/2019	20192	20195	4/13/1955	4/16/1955	Brass	0.0625	1
235	8/14/2019	20193	20198	4/14/1955	4/19/1955	Brass	0.0625	3
81	8/10/2019	20193	20198	4/14/1955	4/19/1955	Brass	0.21875	4
660	7/17/2019	20193	20197	4/14/1955	4/18/1955	Brass	0.21875	4
272	4/6/2019	20192	20194	4/13/1955	4/15/1955	Brass	0.21875	1
819	2/14/2022	20221	20222	5/12/1955	5/13/1955	Plastic	0.1875	1
268	7/5/2019	20193	20197	4/14/1955	4/18/1955	Plastic	0.1875	1
30	12/22/2019	20194	201912	4/15/1955	10/23/2452	Poly	0.125	1
606	1/19/2020	20201	20201	4/22/1955	4/22/1955	Poly	0.125	2
107	7/7/2020	20203	20207	4/24/1955	4/28/1955	Brass	0.21875	1
350	5/18/2019	20192	20195	4/13/1955	4/16/1955	Paper	0.25	1
563	6/26/2020	20202	20206	4/23/1955	4/27/1955	Brass	0.21875	3
250	8/18/2019	20193	20198	4/14/1955	4/19/1955	Brass	0.21875	3
366	6/16/2019	20192	20196	4/13/1955	4/17/1955	Brass	0.21875	4
550	11/10/2021	20214	202111	5/5/1955	5/10/2453	Brass	0.0625	6
492	11/11/2020	20204	202011	4/25/1955	1/30/2453	Wood	0.25	1
428	5/25/2020	20202	20205	4/23/1955	4/26/1955	Steel	0.25	1
235	11/27/2020	20204	202011	4/25/1955	1/30/2453	Paper	0.25	1
826	4/26/2025	20252	20254	6/12/1955	6/14/1955	Wood	0.25	1
243	2/14/2021	20211	20212	5/2/1955	5/3/1955	Brass	0.21875	1
349	4/5/2021	20212	20214	5/3/1955	5/5/1955	Brass	0.21875	1
673	8/21/2022	20223	20228	5/14/1955	5/19/1955	Brass	0.21875	1
828	9/6/2022	20223	20229	5/14/1955	5/20/1955	Brass	0.21875	1
154	12/2/2024	20244	202412	6/4/1955	3/7/2454	Brass	0.0625	2

<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" span="7"></colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
</tbody>

Thank you,

<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" span="7"></colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
</tbody>

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Item	Actual Date	Quarter Text	Month Text	Quarter Value	Month Value	Material	Size	Sum Product Ranking
2​	970​	8/4/2019​	20193​	20198​	4/14/1955​	4/19/1955​	Brass	0.21875​	1​	19.16​
3​	254​	12/2/2018​	20184​	201812​	4/5/1955​	7/15/2452​	Brass	0.09375​	4​	4.37​
4​	345​	1/24/2019​	20191​	20191​	4/12/1955​	4/12/1955​	Brass	0.09375​	6​	10.4​
5​	883​	5/28/2018​	20182​	20185​	4/3/1955​	4/6/1955​	Steel	0.25​	1​	0​
6​	988​	7/20/2018​	20183​	20187​	4/4/1955​	4/8/1955​	Wood	0.25​	1​	1.1​
7​	302​	8/17/2018​	20183​	20188​	4/4/1955​	4/9/1955​	Plastic	0.25​	1​	1.2​
8​	94​	8/11/2018​	20183​	20188​	4/4/1955​	4/9/1955​	Brass	0.0625​	2​	1.2​
9​	242​	11/26/2018​	20184​	201811​	4/5/1955​	7/14/2452​	Brass	0.0625​	2​	4.34​
10​	280​	11/8/2018​	20184​	201811​	4/5/1955​	7/14/2452​	Brass	0.21875​	3​	4.34​
11​	606​	11/10/2018​	20184​	201811​	4/5/1955​	7/14/2452​	Brass	0.21875​	3​	4.34​
12​	347​	10/8/2018​	20184​	201810​	4/5/1955​	7/13/2452​	Plastic	0.1875​	3​	4.32​
13​	965​	10/18/2018​	20184​	201810​	4/5/1955​	7/13/2452​	Plastic	0.1875​	1​	4.32​
14​	479​	4/6/2019​	20192​	20194​	4/13/1955​	4/15/1955​	Poly	0.125​	1​	13.7​
15​	313​	5/3/2019​	20192​	20195​	4/13/1955​	4/16/1955​	Poly	0.125​	1​	13.9​
16​	354​	10/20/2019​	20194​	201910​	4/15/1955​	10/21/2452​	Brass	0.21875​	3​	26.38​
17​	419​	6/8/2020​	20202​	20206​	4/23/1955​	4/27/1955​	Brass	0.09375​	1​	29.23​
18​	201​	7/12/2019​	20193​	20197​	4/14/1955​	4/18/1955​	Brass	0.09375​	3​	19.13​
19​	960​	1/7/2019​	20191​	20191​	4/12/1955​	4/12/1955​	Wood	0.25​	1​	10.4​
20​	593​	2/5/2019​	20191​	20192​	4/12/1955​	4/13/1955​	Plastic	0.25​	1​	10.6​
21​	224​	5/2/2020​	20202​	20205​	4/23/1955​	4/26/1955​	Brass	0.21875​	3​	29.21​
22​	114​	5/10/2019​	20192​	20195​	4/13/1955​	4/16/1955​	Brass	0.0625​	1​	13.9​
23​	235​	8/14/2019​	20193​	20198​	4/14/1955​	4/19/1955​	Brass	0.0625​	3​	19.16​
24​	81​	8/10/2019​	20193​	20198​	4/14/1955​	4/19/1955​	Brass	0.21875​	4​	19.16​
25​	660​	7/17/2019​	20193​	20197​	4/14/1955​	4/18/1955​	Brass	0.21875​	4​	19.13​
26​	272​	4/6/2019​	20192​	20194​	4/13/1955​	4/15/1955​	Brass	0.21875​	1​	13.7​
27​	819​	2/14/2022​	20221​	20222​	5/12/1955​	5/13/1955​	Plastic	0.1875​	1​	39.28​
28​	268​	7/5/2019​	20193​	20197​	4/14/1955​	4/18/1955​	Plastic	0.1875​	1​	19.13​
29​	30​	12/22/2019​	20194​	201912​	4/15/1955​	10/23/2452​	Poly	0.125​	1​	26.39​
30​	606​	1/19/2020​	20201​	20201​	4/22/1955​	4/22/1955​	Poly	0.125​	2​	28.2​
31​	107​	7/7/2020​	20203​	20207​	4/24/1955​	4/28/1955​	Brass	0.21875​	1​	33.25​
32​	350​	5/18/2019​	20192​	20195​	4/13/1955​	4/16/1955​	Paper	0.25​	1​	13.9​
33​	563​	6/26/2020​	20202​	20206​	4/23/1955​	4/27/1955​	Brass	0.21875​	3​	29.23​
34​	250​	8/18/2019​	20193​	20198​	4/14/1955​	4/19/1955​	Brass	0.21875​	3​	19.16​
35​	366​	6/16/2019​	20192​	20196​	4/13/1955​	4/17/1955​	Brass	0.21875​	4​	13.12​
36​	550​	11/10/2021​	20214​	202111​	5/5/1955​	5/10/2453​	Brass	0.0625​	6​	38.42​
37​	492​	11/11/2020​	20204​	202011​	4/25/1955​	1/30/2453​	Wood	0.25​	1​	34.4​
38​	428​	5/25/2020​	20202​	20205​	4/23/1955​	4/26/1955​	Steel	0.25​	1​	29.21​
39​	235​	11/27/2020​	20204​	202011​	4/25/1955​	1/30/2453​	Paper	0.25​	1​	34.4​
40​	826​	4/26/2025​	20252​	20254​	6/12/1955​	6/14/1955​	Wood	0.25​	1​	43.31​
41​	243​	2/14/2021​	20211​	20212​	5/2/1955​	5/3/1955​	Brass	0.21875​	1​	36.26​
42​	349​	4/5/2021​	20212​	20214​	5/3/1955​	5/5/1955​	Brass	0.21875​	1​	37.27​
43​	673​	8/21/2022​	20223​	20228​	5/14/1955​	5/19/1955​	Brass	0.21875​	1​	40.29​
44​	828​	9/6/2022​	20223​	20229​	5/14/1955​	5/20/1955​	Brass	0.21875​	1​	40.3​
45​	154​	12/2/2024​	20244​	202412​	6/4/1955​	3/7/2454​	Brass	0.0625​	2​	42.43​

Sheet: Sheet29

Formula in cell J2:
=(COUNTIF($E$2:$E$45,"<"&E2)&"."&COUNTIF($F$2:$F$45,"<"&F2))*1

 

[DerekWooley]

Wow, that is very cool. How would I add more criteria? I tried the below to bring in material but I got the #value error.

=(COUNTIF($E$3:$E$45,"<"&E3)&"."&COUNTIF($F$3:$F$45,"<"&F3)&"."&COUNTIF($G$3:$G$45,"<"&G3))*1

 

[Oscar Cronquist]

Wow, that is very cool. How would I add more criteria?

Interesting question, I really don't know.