Excel cells maximum sum

Good evening to all,

In excel in cells F8:F9 we have the numbers 17 and 11 respectively (sum 28), in cells M8:M9 we have the numbers 14 and 9 respectively (sum 23) and in cells T8:T9 we have the numbers 17 and 14 respectively (sum 31). I am looking for the formula that will display the maximum number by summing these cells. In our case it should display the number 31 as cells T8:T9 have the maximum sum.
 Also the formula that will display the second largest number by summing these cells. In our case it should display the number 28 as cells F8:F9 have the second largest sum.

Then I look for the formula with which, if the sum between cells F8:F9 and M8:M9 and T8:T9 with the same elements as before (in our case cells T8:T9 have the maximum sum again) then let us returns the value of cell S1, if cells F8:F9 had the largest sum return the value of cell E1 and if cells M8:M9 had the largest sum return the value of cell L1.
 Again here I am looking for the formula that will return me the second largest sum (the 28 in cells F8:F9) so it returns the value of cell E1 and so on.


Thanks in advance


Postscript : I have already tried these formulas unsuccesful
 
=IF(SUM(F8:F9)=MAX(F8:F9;M8:M9;T8:T9);SUM(F8:F9);IF(SUM(M8:M9)=MAX(F8:F9;M8:M9;T8:T9);SUM(M8:M9)=MAX(F8:F9;M8:M9;T8:T9);SUM(T8:T9)))

And

=MAX(SUM(F8:F9);SUM(M8:M9);SUM(T8:T9))

And

=MAX(SUMPRODUCT(F8:F9;{1;1});SUMPRODUCT(M8:M9; {1;1});SUMPRODUCT(T8:T9;{1;1}))

Also my operating system use ';' instead of ',' to separate cells

3 answers

Andrew Wathana Hosakul 0 Reputation points

2024-05-09T05:02:09.4866667+00:00
Since you only have 3 numbers to evaluate, you can use the MEDIAN to identify the 2nd highest value. And use Min, Max to get the highest and lowest.

For the final evaluation, you can use the SWITCH(TRUE()... pattern instead of using nested IF statements.

=SWITCH( TRUE(), SUM($F$8:$F$9)=MAX(SUM($F$8:$F$9),SUM($M$8:$M$9),SUM($T$8:$T$9)),E1, SUM($M$8:$M$9)=MAX(SUM($F$8:$F$9),SUM($M$8:$M$9),SUM($T$8:$T$9)),L1, S1)
Please sign in to rate this answer.
Νικόλαος Γιαννέλος 0 Reputation points

2024-05-12T11:09:06.7166667+00:00

Good morning Andrew, thank you very much for your time on my problem and sorry for the confusion.

To make it easier to understand what I want to do, below I give an example : =MAX(SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76);SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76))

So with this formula I find the maximum number between the 2 merged cells (F8-F9 and F10-F11) and so on. Cells with value 76 (K76-R76-Y76 and so on) contain auxiliary values in case of same sum (0.0000006-0.00000059-0.00000058 and so on)

Next, I want to find the 2nd largest number between those cells, the 3rd,4th,5th,6th and so on. Using the formula to find the 2nd largest number : =LARGE({SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76) ;SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+ SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76)};2) is giving me problem.

In the next step respectively I will find the largest number between the three merged cells (F8-F9 and F10-F11 and F12-F13) with the formula : =MAX(SUM($F$8:$F$13)+SUM($K$76);SUM($M$8:$M$13)+SUM($R$76);SUM($T$8:$T$13)+SUM($Y$76);SUM($AA$8:$AA$13)+SUM($AF$76);SUM($AH$8:$AH$13)+SUM($AM$76);SUM($AO$8:$AO$13)+SUM($AT$76);SUM($AV$8:$AV$13)+SUM($BA$76);SUM($BC$8:$BC$13)+SUM($BH$76);SUM($BJ$8:$BJ$13)+SUM($BO$76);SUM($BQ$8:$BQ$13)+SUM($BV$76);SUM($BX$8:$BX$13)+SUM($CC$76);SUM($CE$8:$CE$13)+SUM($CJ$76);SUM($CL$8:$CL$13)+SUM($CQ$76);SUM($CS$8:$CS$13)+SUM($CX$76);SUM($CZ$8:$CZ$13)+SUM($DE$76);SUM($DG$8:$DG$13)+SUM($DL$76);SUM($DN$8:$DN$13)+SUM($DS$76);SUM($DU$8:$DU$13)+SUM($DZ$76);SUM($EB$8:$EB$13)+SUM($EG$76);SUM($EI$8:$EI$13)+SUM($EN$76)) .

And here also I want to find the 2nd,3rd...6th largest number.

Again, thank you for your time.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Jiajing Hua-MFST 11,045 Reputation points Microsoft Vendor

2024-05-09T05:23:33.0533333+00:00

Hi @Νικόλαος Γιαννέλος

To get the second largest number by summing these cells, the formula could be

=LARGE(CHOOSE({1,2,3},SUM(F8:F9),SUM(M8:M9),SUM(T8:T9)),2)

Then I look for the formula with which, if the sum between cells F8:F9 and M8:M9 and T8:T9 with the same elements as before

I am a little confused about it, I will provide following formulas first:

The second largest number: =INDEX(CHOOSE({1,2,3},E1,L1,S1),,MATCH(LARGE(CHOOSE({1,2,3},SUM(F8:F9),SUM(M8:M9),SUM(T8:T9)),2),CHOOSE({1,2,3},SUM(F8:F9),SUM(M8:M9),SUM(T8:T9))))

The first largest number: =INDEX(CHOOSE({1,2,3},E1,L1,S1),,MATCH(LARGE(CHOOSE({1,2,3},SUM(F8:F9),SUM(M8:M9),SUM(T8:T9)),1),CHOOSE({1,2,3},SUM(F8:F9),SUM(M8:M9),SUM(T8:T9))))

Any misunderstanding, welcome to post back.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Νικόλαος Γιαννέλος 0 Reputation points

2024-05-12T11:11:07.04+00:00

Good morning Jiajing Hua-MFST, thank you very much for your time on my problem and sorry for the confusion.

To make it easier to understand what I want to do, below I give an example : =MAX(SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76);SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76))

So with this formula I find the maximum number between the 2 merged cells (F8-F9 and F10-F11) and so on. Cells with value 76 (K76-R76-Y76 and so on) contain auxiliary values in case of same sum (0.0000006-0.00000059-0.00000058 and so on)

Next, I want to find the 2nd largest number between those cells, the 3rd,4th,5th,6th and so on. Using the formula to find the 2nd largest number : =LARGE({SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76) ;SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+ SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76)};2) is giving me problem.

In the next step respectively I will find the largest number between the three merged cells (F8-F9 and F10-F11 and F12-F13) with the formula : =MAX(SUM($F$8:$F$13)+SUM($K$76);SUM($M$8:$M$13)+SUM($R$76);SUM($T$8:$T$13)+SUM($Y$76);SUM($AA$8:$AA$13)+SUM($AF$76);SUM($AH$8:$AH$13)+SUM($AM$76);SUM($AO$8:$AO$13)+SUM($AT$76);SUM($AV$8:$AV$13)+SUM($BA$76);SUM($BC$8:$BC$13)+SUM($BH$76);SUM($BJ$8:$BJ$13)+SUM($BO$76);SUM($BQ$8:$BQ$13)+SUM($BV$76);SUM($BX$8:$BX$13)+SUM($CC$76);SUM($CE$8:$CE$13)+SUM($CJ$76);SUM($CL$8:$CL$13)+SUM($CQ$76);SUM($CS$8:$CS$13)+SUM($CX$76);SUM($CZ$8:$CZ$13)+SUM($DE$76);SUM($DG$8:$DG$13)+SUM($DL$76);SUM($DN$8:$DN$13)+SUM($DS$76);SUM($DU$8:$DU$13)+SUM($DZ$76);SUM($EB$8:$EB$13)+SUM($EG$76);SUM($EI$8:$EI$13)+SUM($EN$76)) .

And here also I want to find the 2nd,3rd...6th largest number.

Again, thank you for your time.

Jiajing Hua-MFST 11,045 Reputation points Microsoft Vendor

2024-05-14T08:53:30.8866667+00:00

Hi @Νικόλαος Γιαννέλος

I take 4 group data from your formula as an example.

You need to add "Choose" function to create an array as Barry Schwarz explained.

Since I take 4 group of sum number, then the Choose function should be CHOOSE({1,2,3,4},SUM1,SUM2,SUM3,SUM4).

Such as: =CHOOSE({1,2,3,4},SUM($F$8:$F$11)+SUM($K$76),SUM($M$8:$M$11)+SUM($R$76),SUM($T$8:$T$11)+SUM($Y$76),SUM($AA$8:$AA$11)+SUM($AF$76))

IF you want the second large number, then the formula is =LARGE(CHOOSE({1,2,3,4},SUM($F$8:$F$11)+SUM($K$76),SUM($M$8:$M$11)+SUM($R$76),SUM($T$8:$T$11)+SUM($Y$76),SUM($AA$8:$AA$11)+SUM($AF$76)),2)

Plus, if the maximum value, the second maximum value, and the third maximum value are in a column and are consecutive, you can use the row number to set the formula.

For example, I enter the formula =LARGE(CHOOSE({1,2,3,4},SUM($F$8:$F$11)+SUM($K$76),SUM($M$8:$M$11)+SUM($R$76),SUM($T$8:$T$11)+SUM($Y$76),SUM($AA$8:$AA$11)+SUM($AF$76)),ROW()) in first row, then drag down.

Jiajing Hua-MFST 11,045 Reputation points Microsoft Vendor

2024-05-17T07:18:53.6366667+00:00

Hi @Νικόλαος Γιαννέλος

If you have a chance to be back, please check whether my information is helpful to you.

Any further issue, please feel free to post back.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Barry Schwarz 3,071 Reputation points

2024-05-09T08:30:42.0533333+00:00
First of all, the formula =MAX(SUM(F$8:F$9),SUM(M$8:M$9),SUM(T$8:T$9)) works fine. I don't know why you had trouble with it. If you are truly limited to only three sums, you could use the MAX, MEDIAN, and MIN function but this will not help with the second half of your objective.

To solve the general problem, take it in steps:

The formula =CHOOSE({1,2,3},SUM(F$8:F$9),SUM(M$8:M$9),SUM(T$8:T$9)) builds an array of the sums. You can expand this to include more than three. Call this textA.

The formula =LARGE(textA,1) produces the largest sum. You can change the 1 to get the 2nd, 3rd, etc largest sum. Call this textL.

The formula =MATCH(*textL,textA,*0) produces the index number of the largest sum. Again, change the 1 in textL to get the 2nd, 3rd, etc largest. Call this textM.

The formula =CHOOSE(*textM,*E1,L1,S1) will produce the value of the cell in row 1 in the column to the left of the largest sum. Again, change the 1 as desired.

When put all together, you get

=CHOOSE(MATCH(LARGE(CHOOSE({1,2,3},SUM(F$8:F$9),SUM(M$8:M$9),SUM(T$8:T$9)),1),CHOOSE({1,2,3},SUM(F$8:F$9),SUM(M$8:M$9),SUM(T$8:T$9)),0),E$1,L$1,S$1)
Please sign in to rate this answer.
Νικόλαος Γιαννέλος 0 Reputation points

2024-05-12T11:05:01.9633333+00:00

Good morning Μπάρι Σβαρτς, thank you very much for your time on my problem and sorry for the confusion. Indeed the formula =MAX(SUM(F$8:F$9),SUM(M$8:M$9),SUM(T$8:T$9)) works fine, just because of my mistake the cells F9,M9,T9 had to have the value F11,M11,T11 as these cells are merged F8-F9 into one cell, F10-F11 again into one cell and F12-F13 also into one cell.

To make it easier to understand what I want to do, below I give an example : =MAX(SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76);SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76))

So with this formula I find the maximum number between the 2 merged cells (F8-F9 and F10-F11) and so on. Cells with value 76 (K76-R76-Y76 and so on) contain auxiliary values in case of same sum (0.0000006-0.00000059-0.00000058 and so on)

Next, I want to find the 2nd largest number between those cells, the 3rd,4th,5th,6th and so on. Using the formula to find the 2nd largest number : =LARGE({SUM($F$8:$F$11)+SUM($K$76);SUM($M$8:$M$11)+SUM($R$76);SUM($T$8:$T$11)+SUM($Y$76);SUM($AA$8:$AA$11)+SUM($AF$76);SUM($AH$8:$AH$11)+SUM($AM$76);SUM($AO$8:$AO$11)+SUM($AT$76);SUM($AV$8:$AV$11)+SUM($BA$76);SUM($BC$8:$BC$11)+SUM($BH$76);SUM($BJ$8:$BJ$11)+SUM($BO$76);SUM($BQ$8:$BQ$11)+SUM($BV$76);SUM($BX$8:$BX$11)+SUM($CC$76);SUM($CE$8:$CE$11)+SUM($CJ$76);SUM($CL$8:$CL$11)+SUM($CQ$76);SUM($CS$8:$CS$11)+SUM($CX$76);SUM($CZ$8:$CZ$11)+SUM($DE$76);SUM($DG$8:$DG$11)+SUM($DL$76);SUM($DN$8:$DN$11)+SUM($DS$76);SUM($DU$8:$DU$11)+SUM($DZ$76);SUM($EB$8:$EB$11)+SUM($EG$76);SUM($EI$8:$EI$11)+SUM($EN$76)};2)

is giving me problem.

In the next step respectively I will find the largest number between the three merged cells (F8-F9 and F10-F11 and F12-F13) with the formula : =MAX(SUM($F$8:$F$13)+SUM($K$76);SUM($M$8:$M$13)+SUM($R$76);SUM($T$8:$T$13)+SUM($Y$76);SUM($AA$8:$AA$13)+SUM($AF$76);SUM($AH$8:$AH$13)+SUM($AM$76);SUM($AO$8:$AO$13)+SUM($AT$76);SUM($AV$8:$AV$13)+SUM($BA$76);SUM($BC$8:$BC$13)+SUM($BH$76);SUM($BJ$8:$BJ$13)+SUM($BO$76);SUM($BQ$8:$BQ$13)+SUM($BV$76);SUM($BX$8:$BX$13)+SUM($CC$76);SUM($CE$8:$CE$13)+SUM($CJ$76);SUM($CL$8:$CL$13)+SUM($CQ$76);SUM($CS$8:$CS$13)+SUM($CX$76);SUM($CZ$8:$CZ$13)+SUM($DE$76);SUM($DG$8:$DG$13)+SUM($DL$76);SUM($DN$8:$DN$13)+SUM($DS$76);SUM($DU$8:$DU$13)+SUM($DZ$76);SUM($EB$8:$EB$13)+SUM($EG$76);SUM($EI$8:$EI$13)+SUM($EN$76)) .

And here also I want to find the 2nd,3rd...6th largest number.

Again, thank you for your time.

Barry Schwarz 3,071 Reputation points

2024-05-13T00:49:01.4866667+00:00

All your processing involves the same 20 sums. Make life easy on yourself and build a 1x20 array containing these sums. Since you don't seem to go too far down the worksheet, you can use cells A100:T100.

Cell A100 would contain =SUM($F$8:$F$13)+SUM($K$76). B100 would contain =SUM($M$8:$M$13)+SUM($R$76). ...

Then, every nth largest value you are interested is computed with the same simple formula: =LARGE(A100:T100,x) where x is any value between 1 and 20.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Excel cells maximum sum

3 answers

Your answer