• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula: sum of each column's rows that are highest value of that row

D

Dash

New Member
I have a set of data (summary example below) and need to sum for each column of that column's values that are the highest in the rows across all columns.

A1 = 13 B1 = 11 C1 = 12
A2 = 11 B2 = 10 C2 = 12
A3 = 16 B3 = 14 C3 = 18

formula output in A4 should be 13 because the value 13 is highest value on row 1 and the values in A2 and A3 are not the highest values in their row.

formula output in B4 should be 0 because none of the values in B1:B3 are the highest in rows 1:3

formula output in C4 should be 30 because the values 12 in C1 is highest in row 2 plus 18 in C3 is the highest value in row 3

Thank you for help.
 
There's probably more efficient formula. But this should do the trick.
A4:
=SUM(IF(MAX($A$1:$C$1)=A1,A1),IF(MAX($A$2:$C$2)=A2,A2),IF(MAX($A$3:$C$3)=A3,A3))

Copy across.
 
Chihiro said:
There's probably more efficient formula. But this should do the trick.
A4:
=SUM(IF(MAX($A$1:$C$1)=A1,A1),IF(MAX($A$2:$C$2)=A2,A2),IF(MAX($A$3:$C$3)=A3,A3))

Copy across.
Click to expand...


Thank you. I need to elaborate that the example was summary. My data includes 25+ rows and 8+ columns.
 
In that case set up helper column at the end.
=MAX(A1:C1)
Copy down

Then you can set up sum formula at the end:
=SUM(IF(A1:A3=$D$1:$D$3,A1:A3))

Confirmed as array (CSE)
Copy across.

Adjust range reference as needed.
 
Hi!

Another option without helper column could be:
=SUMPRODUCT(--(A1:A3=SUBTOTAL(4,OFFSET($A$1:$C$1,ROW(A1:A3)-MIN(ROW(A1:A3)),))),A1:A3)

And copy it right. You can adjust to your range.

If you want with helper (it's like @Chihiro solution):
Into column D:
=MAX(A1:C1) <-- Copy it down.

Into row 4:
=SUMPRODUCT(--(A1:A3=$D1:$D3),A1:A3) <-- Copy it right.

Blessings!
 
Hello friends,

It is also possible to avoid the OFFSET, and without helper column,

=SUMPRODUCT(--(A1:A3=CHOOSE({1;2;3},MAX($A$1:$C$1),MAX($A$2:$C$2),MAX($A$3:$C$3))),A1:A3)

David
 
Hi ,

What should be the result in A4 if your input data is as follows :

A1 = 13 B1 = 11 C1 = 12
A2 = 12 B2 = 10 C2 = 12
A3 = 16 B3 = 14 C3 = 18

where I have changed the value in cell A2.

Narayan
 
Thank you all for the help.

The situation is selling several items being sold via auction/bid process. Rows = items for sale, columns = bidders' bids.

Chiraro - I did have a "helper" column (actually used to total the highest bids for all items: aggregate proceeds from all bidders from sale of all items) and your idea of array worked with this. Thank you.

John Jario V - I was hoping for formula without "helper" column and your sumproduct/offset idea solves for that. Thank you. That's a keeper!
 
You must log in or register to reply here.
Back
Top