+ Reply to Thread
Results 1 to 7 of 7

Prioritize Columns to add non-blank cells per row

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Prioritize Columns to add non-blank cells per row

    I figure there's a pretty simple formula to resolve this.

    I would like Excel to Sum only 1 non-blank cell per row, based on a column priority.

    Column Priority in the sample below is E/C/D/A/B

    So, the process would be:

    Excel checks E2 (blank), then moves to C2 (blank), then moves to D2 (3) and stops.
    Then in the next row, it checks E3 (blank), then moves to C3 (13) and stops.
    In the 3rd row, it checks E4 (8) and stops.
    In row 4, it checks E5 (1) and stops.
    In row 5, it checks E6 (blank), then moves to C6 (11) and stops.
    In row 6, it checks E7 (blank), then C7 (blank), then to D7 (blank), then to A7 (blank), then to B7 (7) and stops.
    Excel adds 3+13+8+1+11+7 = 43


    a b c d e
    12 10 3
    11 6 13
    6 8 8
    9 25 1
    5 17 11 8
    7

    Your help with this is much appreciated!
    Last edited by hawaean; 08-17-2019 at 03:01 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Prioritize Columns to add non-blank cells per row

    Why does row 6 not use 23 in column E, which is meant to be top priority?

    Can we use helper columns?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Re: Prioritize Columns to add non-blank cells per row

    Sorry. User error. I'll correct the table.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Prioritize Columns to add non-blank cells per row

    Could you answer my other question?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Prioritize Columns to add non-blank cells per row

    One way with a helper column (could be hidden):

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    12
    10
    3
    3
    2
    11
    6
    13
    13
    TOTAL:
    43
    3
    6
    8
    8
    8
    4
    9
    25
    1
    1
    5
    5
    17
    11
    8
    11
    6
    7
    7
    Sheet: Sheet2

    Excel 2016 (Windows) 32 bit
    F
    1
    =IF(E1<>"",E1,IF(C1<>"",C1,IF(D1<>"",D1,IF(A1<>"",A1,IF(B1<>"",B1,0)))))
    Sheet: Sheet2

    Excel 2016 (Windows) 32 bit
    I
    2
    =SUM(F1:F6)
    Sheet: Sheet2

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Prioritize Columns to add non-blank cells per row

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Re: Prioritize Columns to add non-blank cells per row

    Hi Ali,

    I see how that works. Thanks.

    Is there a solution that doesn't require a helper row?

    Here's a more specific example for the formula I'm after.

    I have a sheet that lists contractors, what job they are working on, how much their estimate is, if I hired them and how much it actually cost. Then, this table (which I have on a different tab) has formulas that pull the min and max for each job type. The estimate pulls the average for each job and the actual grabs the cost from ones I actually hire.

    On my summary sheet, this is the table I have:

    Job Low High Estimate Actual
    Paint 1,500 4,500
    Roof 8,500 15,000
    Floor 2,000 8,000 6,000
    Window 1,000 2,000 1,350 1,575
    Landscape 7,500 18,000

    If I need to have a helper column, then it's not a big deal. More so out of curiosity now, is there a formula that would sum the jobs conditionally? If there's an Actual entry, add that; otherwise, add the Estimated number. If none, take the Max, then Min. For the last 2 steps, an average of Min/Max works just as well.

+ 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. Move columns based on blank/non-blank cells
    By reanalyst in forum Excel General
    Replies: 1
    Last Post: 06-01-2017, 04:03 PM
  2. Pivot table prioritize issue
    By krishnaa_kumarr88 in forum Excel General
    Replies: 1
    Last Post: 10-17-2014, 04:41 AM
  3. Pivot table prioritize issue
    By krishnaa_kumarr88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2014, 04:38 AM
  4. Replies: 2
    Last Post: 07-03-2013, 06:17 PM
  5. Prioritize Criterias in Access
    By ewong in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-15-2013, 08:27 PM
  6. Goto Blank COLUMNS not all blank CELLS
    By chrismann85 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2008, 08:30 AM
  7. How to make it 'prioritize' between columns?
    By banestyrelsen in forum Excel General
    Replies: 6
    Last Post: 03-24-2008, 03:46 PM

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