+ Reply to Thread
Results 1 to 7 of 7

Sum column but on values which are max in their row

  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    Belfast
    MS-Off Ver
    Excel 2007
    Posts
    6

    Sum column but on values which are max in their row

    Hi all,

    Long time lurker here - can usually work things out (just about) but stuck on this one! I have searched the forum (and others) but no joy.

    I want to get the sum of a column but only of those values that are the largest in their row.

    A 1 1 2 1
    B 1 5 1 1
    C 1 6 1 1
    D 1 1 5 1

    Total 0 11 7 0


    In this example the total at the bottom is what I want to be able to work out automatically for a larger dataset.

    I hope this is clear!
    Thanks

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Sum column but on values which are max in their row

    Why are the 1st and the Last Values 0?

    Do you want sum of the Largest 2?

    If yes, then maybe try this

    Assuming your data is in A1:D4

    Put this in A6 and drag across

    =IF(LARGE(A1:A4,1)<>SMALL(A1:A4,1),LARGE(A1:A4,1)+LARGE(A1:A4,2),0)
    Cheers!
    Deep Dave

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum column but on values which are max in their row

    g2=MAX(B2:E2)

    h2=if(B2=$G2,$G2,0) and drag across.

    after that just sum column H and so on.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    05-04-2011
    Location
    Belfast
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum column but on values which are max in their row

    Hi - thanks for the super quick replies so far.

    The first and last column are blank because none of their rows have a maximum value in that row.

    oeldere - thanks for your reply, but if I read it correctly it will turn the maximum for the whole table rather than keeping it in each row (for example above it would give 18?).

    I want to keep the totals in each column if possible.

    Thanks

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum column but on values which are max in their row

    It would have been better if you added the excel file, but since I am in a very good mood, here it goes.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-04-2011
    Location
    Belfast
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum column but on values which are max in their row

    oeldere,

    Thanks for the solution - sorry I did not understand your original explanation - the excel file was very helpful. I'm glad you got of the right side of the bed and were in a good mood today!

    I had not thought of creating 'extra' columns to do the working out. I think I try to restrict myself to 'elegant' solution - which I see as solutions containing the formula in one cell! Which of course is not always possible, or straightforward.

    Thanks for pointing me in the right direction. I'll just have to hide those extra columns when I'm done with them so my sheet still looks pretty!

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum column but on values which are max in their row

    Thanks for the rep.

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose values in column to rows, and split cell values to extend column
    By SAMMM in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-03-2015, 12:52 AM
  2. [SOLVED] Find Values in Column A and fill identical values in Column B
    By deviltronics in forum Excel General
    Replies: 6
    Last Post: 10-07-2014, 05:25 PM
  3. Replies: 2
    Last Post: 12-30-2013, 08:15 AM
  4. Replies: 10
    Last Post: 07-16-2013, 03:19 PM
  5. Macro that will copy values in Column J and paste values to Column B in new sheet
    By Phixtit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2010, 04:56 PM
  6. assigning date entries to week values and month values to sum column C-N values C-
    By the accountant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2010, 09:52 AM
  7. Replies: 4
    Last Post: 01-29-2008, 08:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1