Sum of the greatest value in one column, plus the sum of the other values in another column

user_3958567 0
Viewed 86

Consider the following sheet/table:

   A    B
1  90   71
2  40   25
3  60   16
4  110  13
5  87   82

I want to have a general formula in cell C1 that sums the greatest value in column A (which is 110), plus the sum of the other values in column B (which are 71, 25, 16 and 82). I would appreciate if the formula wasn't an array formula (as in requiring Ctrl + Shift + Enter). I don’t have Office 365, I have Excel 2019.

My attempt

Getting the greatest value in column A is easy, we use MAX(A1:A5).

So the formula I want in cell C1 should be something like:

=MAX(A1:A5) + SUM(array_of_values_to_be_summed)

Obtaining the values of the other rows in column B (what I called array_of_values_to_be_summed in the previous formula) is the hard part. I've read about using INDEX, MATCH, their combination, and obtaining arrays by using parenthesis and equal signs, and I've tried that, without success so far.

For example, I noticed that NOT((A1:A5 = MAX(A1:A5))) yields an array/list containing ones (or TRUEs) for the relative position of the rows to be summed, and containing a zero (or FALSE) for the relative position of the row to be omitted. Maybe this is useful, I couldn't find how.

Any ideas? Thanks.

Edit 1 (solution)

I managed to obtain what I wanted. I simply multiplied the array obtained with the NOT formula, by the range B1:B5. The final formula is:

=MAX(A1:A5) + SUM(NOT((A1:A5 = MAX(A1:A5))) * B1:B5)

Edit 2 (duplicate values)

I forgot to explain what the formula should do if there are duplicates in column A. In that case, the first term of my final formula (the term that has the MAX function) would be the one whose corresponding value in column B is smallest, and the value in column B of the other duplicates would be used in the second term (the one containing the SUM function).

For example, consider the following sheet/table:

   A    B
1  90   71
2  110  25
3  60   16
4  110  13
5  110  82

Based on the above table, the formula should yield 110 + (71 + 25 + 16 + 82) = 304.

Just to give context, the reason I want such a formula is because I’m writing a spreadsheet that automatically calculates the electric current rating of the short-circuit protective device of the feeder of a group of electric motors in a house or building or mall, as required by the article 430.62(A) of the US National Electrical Code. Column A is the current rating of the short-circuit protective device of the branch-circuit of each motors, and column B is the full-load current of each motor.

excel-formula
3 Answers

You can use this formula

=MAX(A1:A5)
  +SUM(B1:B5)
  -AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1)

enter image description here

Based on @Anupam Chand's hint for max-value-duplicates there could also be min-value-duplicates in column B for corresponding max-value-duplicates in column A. :) This formula would account for that

=SUM(B1:B5)
   +(MAX(A1:A5)-AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1))
   *SUMPRODUCT((A1:A5=MAX(A1:A5))*(B1:B5=AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1)))

enter image description here

Or with @Anupam Chand's shorter and better readable and overall better style :)

=SUM(B1:B5)
  +(MAX(A1:A5)-MINIFS(B1:B5,A1:A5,MAX(A1:A5)))
  *COUNTIFS(A1:A5,MAX(A1:A5),B1:B5,MINIFS(B1:B5,A1:A5,MAX(A1:A5)))

The explanation works for bot solutions:

The SUM-part just sums the whole list.

The second line gets the max-value for column A and the corresponding min-value of column B for the max-values in column A and adds or subtracts it respectively.

The third line counts, how many times the corresponding min-value for the max-value occurs and multiplies it with the second line.

user_100685200

Can you try this ?

=MAX(A1:A5)+SUM(B1:B5)-MINIFS(B1:B5,A1:A5,MAX(A1:A5))

What we're doing is adding the max of A to all rows of B and then subtracting the min value of B where A is the max.

user_143586770

If you have Excel 365 you can use the following LET-Formula

=LET(A,A1:A5,
B,B1:B5,
MaxA,MAX(A),
MinBExclude, MINIFS(B,A,MaxA),
sumB1,SUMPRODUCT(B*(A=MaxA)*(B<>MinBExclude)),
sumB2,SUMPRODUCT(B*(A<>MaxA)),
MaxA +sumB1+sumB2

A and B are shortcuts for the two ranges

MaxA returns the max value for A (110)

MinBExclude filters the values of column B by the MaxA-value (25, 13, 82) and returns the min-value of the filtered result (13)

sumB1 returns the sum of the other MaxA values from column B (26 + 82)

sumB2 returns the sum of the values from B where value in A <> MaxA (71 + 60)

and finally the result is returned

If you don't have Excel 365 you can add helper columns for MaxA, MinBExclude, sumB1 and sumB2 and the final result

user_165784240
Related