Top 10 sales per quarter on full year data set

[josh_oneill]

Dear All,

I am trying to retrieve the top 5 customers/ opportunities per quarter based on the commitment type.
In the example below I want to pull the top 5 customers by sales in Q2 (referencing Cell E2 for quarter) and referencing the commitment type (Cell B3), I have tried an Index, Match & Large function but excel did not like it, I'm not sure if this is because there would be multiple quarters with the same customer name or it was a basic formula error?

Can you please help?

Desired output:

			Top 5 Opportunities
				Q1​		Q2​		Q3​		Q4​
Type:​	Commit​		Customer X​	$ X.XX​
			Customer X​	$ X.XX​
			Customer X​	$ X.XX​
			Customer X​	$ X.XX​
			Customer X​	$ X.XX​

Sample Raw Data:

Raw Data​
Customer Name​	Opportunity Name​	Commitment Type​	Quarter​	Sales ($)​
Customer 1​	1​	Commit​	Q1​	10​
Customer 1​	2​	Commit​	Q1​	8​
Customer 2​	3​	Upside​	Q1​	15​
Customer 3​	4​	Commit​	Q1​	1​
Customer 4​	5​	Commit​	Q1​	4​
Customer 5​	6​	Upside​	Q1​	28​
Customer 5​	7​	Commit​	Q1​	9​
Customer 6​	8​	Commit​	Q1​	56​
Customer 7​	9​	Commit​	Q1​	300​
Customer 8​	10​	Upside​	Q1​	41​
Customer 9​	11​	Upside​	Q1​	41​
Customer 10​	12​	Upside​	Q1​	58​
Customer 1​	13​	Upside​	Q2​	9​
Customer 2​	14​	Upside​	Q2​	9​
Customer 3​	15​	Commit​	Q2​	11​
Customer 4​	16​	Commit​	Q2​	42​
Customer 5​	17​	Commit​	Q2​	88​
Customer 6​	18​	Commit​	Q2​	4000​
Customer 7​	19​	Commit​	Q2​	100​
Customer 7​	20​	Commit​	Q2​	300​
Customer 8​	21​	Upside​	Q2​	10​
Customer 9​	22​	Upside​	Q2​	3​
Customer 9​	23​	Upside​	Q2​	2​
Customer 10​	24​	Upside​	Q3​	19​

Thanks!

 

[sandy666]

something like this?

Sum of Sales ($)			Quarter
Customer Name	Opportunity Name	Commitment Type	Q1	Q2	Q3
Customer 10	12	Upside	58
	24	Upside			19
Customer 5	6	Upside	28
	7	Commit	9
	17	Commit		88
Customer 6	8	Commit	56
	18	Commit		4000
Customer 7	9	Commit	300
	19	Commit		100
	20	Commit		300
Customer 8	10	Upside	41
	21	Upside		10

 

[josh_oneill]

something like this?

Sum of Sales ($)			Quarter
Customer Name	Opportunity Name	Commitment Type	Q1	Q2	Q3
Customer 10	12	Upside	58
	24	Upside			19
Customer 5	6	Upside	28
	7	Commit	9
	17	Commit		88
Customer 6	8	Commit	56
	18	Commit		4000
Customer 7	9	Commit	300
	19	Commit		100
	20	Commit		300
Customer 8	10	Upside	41
	21	Upside		10

Not quite, It would be different customers/ opportunities per quarter. Not looking at the same customer for each quarter if that makes sense?

 

[sandy666]

so I misunderstood, sorry