Hai frend.. another pepper work need to simplify... 
In my provided sample workbook, One single item contain 3 type of data that is Q=quantity, C=Cost Price & R=Retail Price
Now, i need to fill-up total amount of particular item but from 'R' type only on to the Closing Stock column (sheet named Softline).
Then the amount need to ranked, mean no 1 is the highest then no 5 is the 5th highest amount. In my mind is just use LARGE function to get the amount ranking. I had try this firmula: {=IFERROR(LARGE(IF(Data!$D:$D=$A$3, Data!$O:$O, Data!$N:$N="R"), $B3),0)} to get retail amount "R" but not successful. This kind of formula will include the cost 'C' on to closing stock column even though I filter by using method Data!$N:$N="R"
Maybe I use wrong formula.. 
So, that way I need help from yours to show me the right formula function...
If you create a custom function, you can get it pretty easily. I built one below.
If you then put this formula in cell G3, =RankThatSucker(H3,Data!A:A,$A$3,Data!E:E,"R",Data!F:F)
I attached an updated version and built the function here: https://pastebin.com/4QYisbqd
Here's the text version:
Function RankThatSucker(theRank As Integer, DivisionRange As Range, DivisionNumber As Variant, CategoryRange As Range, CategoryType As Variant, DataRange As Range) As Double
Dim WS As Worksheet, rCell As Range, Cat_Column As Integer, Div_Column As Integer, i As Long
Set WS = Sheets(CategoryRange.Parent.Name)
Set DataRange = Intersect(DataRange, WS.UsedRange)
Cat_Column = CategoryRange.Cells(1, 1).Column
Div_Column = DivisionRange.Cells(1, 1).Column
ReDim prime_array(Application.WorksheetFunction.CountIf(Intersect(CategoryRange, WS.UsedRange), CategoryType)) As Double
For Each rCell In DataRange.Cells
If WS.Cells(rCell.Row, Div_Column).Value2 = DivisionNumber Then
If WS.Cells(rCell.Row, Cat_Column).Value2 = CategoryType Then
If IsNumeric(rCell) Then
prime_array(i) = rCell.Value2
i = i + 1
End If
End If
End If
Next rCell
RankThatSucker = Application.WorksheetFunction.Large(prime_array(), theRank)
End Function
PGCodeRider said
If you create a custom function, you can get it pretty easily. I built one below.If you then put this formula in cell G3, =RankThatSucker(H3,Data!A:A,$A$3,Data!E:E,"R",Data!F:F)
I attached an updated version and built the function here: https://pastebin.com/4QYisbqd
Here's the text version:
Function RankThatSucker(theRank As Integer, DivisionRange As Range, DivisionNumber As Variant, CategoryRange As Range, CategoryType As Variant, DataRange As Range) As Double Dim WS As Worksheet, rCell As Range, Cat_Column As Integer, Div_Column As Integer, i As Long Set WS = Sheets(CategoryRange.Parent.Name) Set DataRange = Intersect(DataRange, WS.UsedRange) Cat_Column = CategoryRange.Cells(1, 1).Column Div_Column = DivisionRange.Cells(1, 1).Column ReDim prime_array(Application.WorksheetFunction.CountIf(Intersect(CategoryRange, WS.UsedRange), CategoryType)) As Double For Each rCell In DataRange.Cells If WS.Cells(rCell.Row, Div_Column).Value2 = DivisionNumber Then If WS.Cells(rCell.Row, Cat_Column).Value2 = CategoryType Then If IsNumeric(rCell) Then prime_array(i) = rCell.Value2 i = i + 1 End If End If End If Next rCell RankThatSucker = Application.WorksheetFunction.Large(prime_array(), theRank) End FunctionHai PGCodeRider.. Thanks on you help and teach.. It very simple and easy.. m/.. very cool man..
However, once again I need your help to place the Inventory Disposal (Data! V: V) value to the Inventory Disposal column in the sheet named BS Store outlet. Data obtained also need to be ranked. That means, the higher of negative amount will be at 1st rank.
Whay value at Inventory Disposal (at Data sheet) is -ve signed? This is because every inventory disposed due to expired date causes the company's profit reduction. Any transactions that losses company profit will be in the (-ve) sign.
As before, it has used the formula {= IFERROR (SMALL (IF (Data! F: F = $ A $ 3, Data! $ P: $ P = "R", Data! $ V: $ V), $ B3) )} to get retail amount "R" but not successful. This Kind of formula will gate the value from entire division.
So, one again.. pls teach me how to done this workbook..
