Locate highest and lowest showing values in numbers and text

[Caly]

Hello I have a table showing items in rows and sizes in column headers along with a breakout for quantities and sales. I’m trying to show a summary where a formula will have a summary to show the highest seller, second, third and so on all the way to the lowest.

Is there a max or min formula for this or an index match? I’m confused and stumped with this to do a formula. Thank you to all in advance.

Below is the data

Type	Qty S	Qty M	Qty L	Sales S	Sales M	Sales L
Blue	60	5	77	4.56	11.12	23.45
Yellow	3	0	4356	3.25	10.98	16.55

 

[smitpau]

Maybe you've already thought of this but could you sum up the S,M & L sales in another column to the right of Sales L and sort by that?

 

[smitpau]

You can use = Large(the range with the total sale,rank (1st is largest, 2nd is 2nd largest etc).

You could use a helper column to go from 1 to the total number of items you have (maybe more if you want future capacity) to reference in the Large formula.
Then you could use the MATCH function to match the total number with the row it's from and wrap than in an Index so you can then pull in other data from the other columns like Qrts S etc.

 

[Caly]

Thank you I didn’t no but not sure if I’m thinking about that approach in how you are suggesting which may be great

Looking to have a summary showing which line has the highest and lowest

So looking to see in the example the below but trying to make a formula way so when there’s multiple lines it will have a summary populate

Looking for the below;
1. Yellow, L, 4356 Qty, 16.55 sales
2. Blue, L, 77 Qty, 23.45 sales
3. Blue, S, 60 Qty, 11.12 sales

 

[alansidman]

Would the following presentation work for you?

Book2
	A	B	C	D	E	F	G
1	Type	Qty S	Qty M	Qty L	Sales S	Sales M	Sales L
2	Blue	60	5	77	4.56	11.12	23.45
3	Yellow	3	0	4356	3.25	10.98	16.55
4
5	Type	Attribute	Value
6	Blue	Qty S	60
7	Blue	Qty M	5
8	Blue	Qty L	77
9	Blue	Sales S	4.56
10	Blue	Sales M	11.12
11	Blue	Sales L	23.45
12	Yellow	Qty S	3
13	Yellow	Qty M	0
14	Yellow	Qty L	4356
15	Yellow	Sales S	3.25
16	Yellow	Sales M	10.98
17	Yellow	Sales L	16.55
Sheet1

 

[alansidman]

Disregard my earlier post, I mis-read your requirements.

 

[Caly]

Thank you all. Is there a way to apply the large or small function? Or a match?

 

[Peter_SSs]

With MS365, is this what you want?

23 07 08.xlsm
	A	B	C	D	E	F	G
1	Type	Qty S	Qty M	Qty L	Sales S	Sales M	Sales L
2	Blue	60	5	77	4.56	11.12	23.45
3	Yellow	3	0	4356	3.25	10.98	16.55
4
5
6	Type	Size	Qty	Sales
7	Yellow	L	4356	16.55
8	Blue	L	77	23.45
9	Blue	S	60	4.56
10	Blue	M	5	11.12
11	Yellow	S	3	3.25
12	Yellow	M	0	10.98
Sort by Qty

Cell Formulas
Range	Formula
A7:D12	A7	=LET(t,TEXTSPLIT(TEXTJOIN("|",,TOCOL(A2:A3&" "&B2:D3&" "&B1:D1&" "&E2:G3))," ","|"),CHOOSECOLS(SORT(IFERROR(t+0,t),2,-1),1,4,2,5))
Dynamic array formulas.

 

[Caly]

Thank you so much. What is the formula for the let (t.

 

[Peter_SSs]

What is the formula for the let (t.

I don't understand the question. let(t isn't a formula, it is just part of the longer formula.

Did the whole formula that I posted do what you want?