DerekWooley
New Member
- Joined
- May 1, 2018
- Messages
- 34
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.
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>