Large function

[ripdaman]

I have a set of random numbers in two columns A and B. I want to arrange the sum of these two columns in descending order. However, if the sum of two numbers in Column A and B is equal to another row(s), then the descending order should give priority to the greater number in Column A. For example, in the table below, sum of two numbers in Column A and B in first two rows is equal (38). However, the descending order in Column C should keep row 2 as No.1 as row 2 in Column A has the greater number (22) as compared to row 1 in Column A (20).

Thanks in advance.

A	B
20	18
22	16
19	22
15	26
18	23
23	14
26	11
25	15
27	18
29	9

 

[Scott Huish]

If you are arranging the sums in descending order, why does it matter which it comes from, it's the same number?
=SORT(A1:A10+B1:B10,,-1)

20​	18​	45​
22​	16​	41​
19​	22​	41​
15​	26​	41​
18​	23​	40​
23​	14​	38​
26​	11​	38​
25​	15​	38​
27​	18​	37​
29​	9​	37​

 

[ripdaman]

If you are arranging the sums in descending order, why does it matter which it comes from, it's the same number?
=SORT(A1:A10+B1:B10,,-1)

20​	18​	45​
22​	16​	41​
19​	22​	41​
15​	26​	41​
18​	23​	40​
23​	14​	38​
26​	11​	38​
25​	15​	38​
27​	18​	37​
29​	9​	37​

This is required as values in Columns A and B are readings on a particular day. If the sum of two values are equal then the index function gives the same date for similar values.

 

[Eric W]

Are you just looking to sort your table? If so, add a formula in column C, =A1+B1 , and copy down. Then use the Sort tool to sort the range by column C, then by column A.

If you want to do it with a formula, you can do something like:

Book1
	A	B	C	D	E	F	G	H	I
1	A	B	Date			A	B	Date
2	20	18	1/1/2024			27	18	1/9/2024	45
3	22	16	1/2/2024			19	22	1/3/2024	41
4	19	22	1/3/2024			18	23	1/5/2024	41
5	15	26	1/4/2024			15	26	1/4/2024	41
6	18	23	1/5/2024			25	15	1/8/2024	40
7	23	14	1/6/2024			29	9	1/10/2024	38
8	26	11	1/7/2024			22	16	1/2/2024	38
9	25	15	1/8/2024			20	18	1/1/2024	38
10	27	18	1/9/2024			26	11	1/7/2024	37
11	29	9	1/10/2024			23	14	1/6/2024	37
Sheet5

Cell Formulas
Range	Formula
F2:H11	F2	=SORTBY(A2:C11,A2:A11+B2:B11,-1,A2:A11,-1)
I2:I11	I2	=INDEX(F2#,0,1)+INDEX(F2#,0,2)
Dynamic array formulas.

The I formula is optional.

 

[ripdaman]

Are you just looking to sort your table? If so, add a formula in column C, =A1+B1 , and copy down. Then use the Sort tool to sort the range by column C, then by column A.

If you want to do it with a formula, you can do something like:

Book1
	A	B	C	D	E	F	G	H	I
1	A	B	Date			A	B	Date
2	20	18	1/1/2024			27	18	1/9/2024	45
3	22	16	1/2/2024			19	22	1/3/2024	41
4	19	22	1/3/2024			18	23	1/5/2024	41
5	15	26	1/4/2024			15	26	1/4/2024	41
6	18	23	1/5/2024			25	15	1/8/2024	40
7	23	14	1/6/2024			29	9	1/10/2024	38
8	26	11	1/7/2024			22	16	1/2/2024	38
9	25	15	1/8/2024			20	18	1/1/2024	38
10	27	18	1/9/2024			26	11	1/7/2024	37
11	29	9	1/10/2024			23	14	1/6/2024	37
Sheet5

Cell Formulas
Range	Formula
F2:H11	F2	=SORTBY(A2:C11,A2:A11+B2:B11,-1,A2:A11,-1)
I2:I11	I2	=INDEX(F2#,0,1)+INDEX(F2#,0,2)
Dynamic array formulas.

The I formula is optional.

Thank you very much for your perfect response to my query. One additional query: If my data has zero values and i want to arrange my data in ascending order excluding zeroes, is there a way i can do it?

 

[Eric W]

Ascending order, as opposed to descending like the example? It's easy enough to do with a formula, we just add a FILTER to it to strip out the zero values. If you want to do it without a formula, we could use the Filter tool, then copy the results to another range, then use the sort tool. If you have an example of what you'd like, I'll take a look at it.

 

[ripdaman]

Ascending order, as opposed to descending like the example? It's easy enough to do with a formula, we just add a FILTER to it to strip out the zero values. If you want to do it without a formula, we could use the Filter tool, then copy the results to another range, then use the sort tool. If you have an example of what you'd like, I'll take a look at it.

My Apologies for the delayed response.

This is a sample data I want in ascending order excluding zeroes.

Thanks

12-25-2022	11.10
01-12-2021	11.10
12-27-2019	11.10
12-29-2019	11.10
01-16-2022	11.00
01-18-2022	11.00
01-10-2024	10.80
01-09-2024	10.50
01-21-2024	9.40
12-25-2019	9.00
12-26-2019	8.80
01-24-2024	8.40
01-01-2027	0.00
01-02-2027	0.00
01-03-2027	0.00
01-04-2027	0.00
01-05-2027	0.00
01-06-2027	0.00
01-07-2027	0.00
01-08-2027	0.00

 

[Eric W]

With a formula:

Book1
	A	B	C	D	E
1	12/25/2022	11.10		1/24/2024	8.40
2	1/12/2021	11.10		12/26/2019	8.80
3	12/27/2019	11.10		12/25/2019	9.00
4	12/29/2019	11.10		1/21/2024	9.40
5	1/16/2022	11.00		1/9/2024	10.50
6	1/18/2022	11.00		1/10/2024	10.80
7	1/10/2024	10.80		1/16/2022	11.00
8	1/9/2024	10.50		1/18/2022	11.00
9	1/21/2024	9.40		12/25/2022	11.10
10	12/25/2019	9.00		1/12/2021	11.10
11	12/26/2019	8.80		12/27/2019	11.10
12	1/24/2024	8.40		12/29/2019	11.10
13	1/1/2027	0.00
14	1/2/2027	0.00
15	1/3/2027	0.00
16	1/4/2027	0.00
17	1/5/2027	0.00
18	1/6/2027	0.00
19	1/7/2027	0.00
20	1/8/2027	0.00
Sheet5

Cell Formulas
Range	Formula
D1:E12	D1	=SORT(FILTER(A1:B20,B1:B20<>0),2,1)
Dynamic array formulas.

If you want to do it without a formula, I'd just filter column B to show only the non-zero rows, copy that, paste it to another range, then sort that by the second column.