Rank Sales between Months

john-paul

New Member
Joined
Nov 23, 2008
Messages
26
Hi,

I would like to rank sales $'s for a selected product, based on date ranges.

Selections can be made in
A1 - Product type (eg: car)
A2 - Date from (EOMONTH dates eg: 31-Jan-17)
A3 - Date to (EOMONTH dates eg: 30-Jun-17)

In row 4 are headings, and row 5 ownards:
Column A - EOMONTH dates, 31-Jan-17, 28-Feb-17, etc
Column B - Product (eg: Car, truck, etc)
Column C - Sales amounts in $'s.

In Column D I would like a ranking based on sales $'s (highest to lowest) only for those records that meet the criteria.

I hope this makes sense.
Thanks,
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,559
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
A data sample for testing purposes along with expected results would be helpful.

M.
 

john-paul

New Member
Joined
Nov 23, 2008
Messages
26
I would like to produce a ranking in column D based on the sales $'s in column C where the sale is Car and it is within the selected date range in A2 and A3.
My attempt is below, but it shouldn't rank if Truck, only if car.
Thanks,

Car<- Product Selection
31-Jan-17<- From Date
30-Jun-17<- To Date
DatesProduct Sales Rank
31-Jan-17Car $ 100
9
31-Jan-17Car $ 1057
31-Jan-17Truck $ 9511
31-Jan-17Truck $ 1018
31-Jan-17Car $ 1204
28-Feb-17Truck $ 1351
28-Feb-17Car $ 1243
28-Feb-17Truck $ 9810
31-Mar-17Car $ 9014
31-Mar-17Truck $ 9412
31-Mar-17Truck $ 1282
30-Apr-17Car $ 9313
31-May-17Truck $ 1166
30-Jun-17Car $ 1185
31-Jul-17Car $ 92
31-Aug-17Car $ 104
30-Sep-17Truck $ 103

<tbody>
</tbody>
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,559
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try


A
B
C
D
1
Car​
<- Product Selection​
2
31/01/2017​
<- From Date​
3
30/06/2017​
<- To Date​
4
Dates​
Product​
Sales​
Rank​
5
31/01/2017​
Car​
100​
5​
6
31/01/2017​
Car​
105​
4​
7
31/01/2017​
Truck​
95​
8
31/01/2017​
Truck​
101​
9
31/01/2017​
Car​
120​
2​
10
28/02/2017​
Truck​
135​
11
28/02/2017​
Car​
124​
1​
12
28/02/2017​
Truck​
98​
13
31/03/2017​
Car​
90​
7​
14
31/03/2017​
Truck​
94​
15
31/03/2017​
Truck​
128​
16
30/04/2017​
Car​
93​
6​
17
31/05/2017​
Truck​
116​
18
30/06/2017​
Car​
118​
3​
19
31/07/2017​
Car​
92​
20
31/08/2017​
Car​
104​
21
30/09/2017​
Truck​
103​

<tbody>
</tbody>


Formula in D5 copied down
=IF(OR(B5<>A$1,A$2>A5,A5>A$3),"",COUNTIFS(A$5:A$21,">="&A$2,A$5:A$21,"<="&A$3,B$5:B$21,A$1,C$5:C$21,">"&C5)+1)
<a$2,a5>
M.</a$2,a5>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,936
Messages
5,656,011
Members
418,264
Latest member
Reiper79

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
Top