How to get value of top 2 vendors spend for a particular item

[#1]

How to get value of top 2 vendors spend for a particular item.

This is my sample data

Item ID	Vendoe name	Spend
5112	Vendor 1	3,000.00
5112	Vendor 2	20,000.00
5112	Vendor 3	25,000.00
6113	Vendor 1	34,560.00
7889	Vendor 1	56,000.00
9887	Vendor 1	670,000.00
6113	Vendor 2	34,520.00
9887	Vendor 2	120,000.00
7889	Vendor 2	3,000.00
5112	Vendor 4	75,000.00
7889	Vendor 3	10,000.00
9887	Vendor 3	14,000.00
6113	Vendor 3	98,760.00
3456	Vendor 1	34,560.00
6113	Vendor 4	100,000.00
7889	Vendor 4	2,000.00
4562	Vendor 1	456,890.00
6113	Vendor 5	2,000.00
5112	Vendor 5	60,000.00
3452	Vendor 1	23,480.00
9870	Vendor 1	18,000.00
9870	Vendor 2	26,000.00
3452	Vendor 2	35,000.00
6113	Vendor 6	5,000.00
9887	Vendor 4	12,556.00

Here i want to get total spend of top 2 vendors based on spend (i.e vendor 4 and vendor 3) for  an item ID 6113. I don't want to write 6113 in formula, it should be automatic, it is 3rd item based on total spend.

[#2]

I don't want to write the item ID number in formula.Is this possible? it is 3 rd spend item based on spend.

Thanks in advance

Regards

Nagarjuna

[MK_QSL]

You mean you want the sum of top 2 vendor for 3rd spend item id?

[MK_QSL]

=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)

[#5]

Yes

[MK_QSL]

I have replied.. check

[MK_QSL]

Have you tried this?

=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)

[#8]

Not working

[#9]

Sorry , its working.

Thank you.

[#10]

I have a doubt,why you have taken number 4 after spend in your formula.

[#11]

Now i want total spend of item 6113.