Rank Sales between Months

[john-paul]

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,

 

[Marcelo Branco]

A data sample for testing purposes along with expected results would be helpful.

M.

 

[john-paul]

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,

[TABLE="width: 334"]
<tbody>[TR]
[TD]Car[/TD]
[TD="colspan: 2"]<- Product Selection[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]<- From Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-Jun-17[/TD]
[TD]<- To Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dates[/TD]
[TD]Product[/TD]
[TD] Sales[/TD]
[TD] Rank[/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]Car[/TD]
[TD] $ 100
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]Car[/TD]
[TD] $ 105[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]Truck[/TD]
[TD] $ 95[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]Truck[/TD]
[TD] $ 101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]31-Jan-17[/TD]
[TD]Car[/TD]
[TD] $ 120[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]28-Feb-17[/TD]
[TD]Truck[/TD]
[TD] $ 135[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]28-Feb-17[/TD]
[TD]Car[/TD]
[TD] $ 124[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]28-Feb-17[/TD]
[TD]Truck[/TD]
[TD] $ 98[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]31-Mar-17[/TD]
[TD]Car[/TD]
[TD] $ 90[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]31-Mar-17[/TD]
[TD]Truck[/TD]
[TD] $ 94[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]31-Mar-17[/TD]
[TD]Truck[/TD]
[TD] $ 128[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]30-Apr-17[/TD]
[TD]Car[/TD]
[TD] $ 93[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]31-May-17[/TD]
[TD]Truck[/TD]
[TD] $ 116[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]30-Jun-17[/TD]
[TD]Car[/TD]
[TD] $ 118[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]31-Jul-17[/TD]
[TD]Car[/TD]
[TD] $ 92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31-Aug-17[/TD]
[TD]Car[/TD]
[TD] $ 104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-Sep-17[/TD]
[TD]Truck[/TD]
[TD] $ 103[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Car​

[/TD]
[TD]

<- Product Selection​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

<- From Date​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

30/06/2017​

[/TD]
[TD]

<- To Date​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Dates​

[/TD]
[TD]

Product​

[/TD]
[TD]

Sales​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

100​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

105​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

95​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

31/01/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

120​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

28/02/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

135​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

28/02/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

124​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

28/02/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

98​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

31/03/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

90​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

31/03/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

94​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

31/03/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

128​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

30/04/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

93​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

31/05/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

116​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

30/06/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

118​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

31/07/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

92​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

31/08/2017​

[/TD]
[TD]

Car​

[/TD]
[TD]

104​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

30/09/2017​

[/TD]
[TD]

Truck​

[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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>

 

[john-paul]

That is great, thank you very much.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.