+ Reply to Thread
Results 1 to 4 of 4

Find lowest in 2 or more columns within each row of sheet, sum the total of all the lowest

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    Savannah, GA
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    12

    Find lowest in 2 or more columns within each row of sheet, sum the total of all the lowest

    I am keeping a pilot log, and would like to be able to easily add up various combinations of totals (ex. cross-country at night, cross-country as pilot-in-command, multi-engine instrument). Each type of flying is logged separately in columns by date in rows. I've attached a simplified version to work with.

    What I think I need is a way to find the lower(est) entry of 2 or more columns (ex. B1 and D1), for multiple rows (i.e. all rows in the sheet), and sum all the lower(est) entries to come up with a grand total. It would need to be able to treat blanks as zero.

    I've attached a sample excel file and image. In the sample sheet, I've manually added to find results for "Cross-country and Night" and for Instrument and "Pilot-in-Command." In a perfect world, I'd love to be able to choose various criteria from dropdowns like in L2, M2, N2 and it retrieve a total as described above.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,924

    Re: Find lowest in 2 or more columns within each row of sheet, sum the total of all the lo

    Using Power Query, here is the Mcode and the file for your review.

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Date X-country Day Night Pilot in Command Instrument X-Country and Night Custom
    2
    1/20/2020
    4
    3
    1
    4
    2
    5
    6
    3
    1/24/2020
    1
    1
    0
    1
    0
    1
    1
    4
    2/2/2020
    0
    2
    0
    2
    2
    0
    4
    5
    2/29/2020
    2
    0
    2
    0
    0
    4
    0
    6
    3/5/2020
    2
    1
    1
    2
    1
    3
    3
    7
    3/6/2020
    1
    1
    0
    1
    0
    1
    1
    8
    4/23/2020
    1
    1
    0
    1
    0
    1
    1
    9
    5/20/2020
    2
    1
    1
    2
    2
    3
    4
    10
    6/1/2020
    0
    1
    1
    0
    0
    1
    0
    11
    6/3/2020
    3
    1
    2
    3
    2
    5
    5
    Sheet: Sheet2
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-29-2013
    Location
    Savannah, GA
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    12

    Re: Find lowest in 2 or more columns within each row of sheet, sum the total of all the lo

    alansidman, thanks for the reply! Forgive me, I probably just don't know how to use what I'm looking at, but I don't understand how/if the file you attached provides a total of all the lowest of each queried row.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,924

    Re: Find lowest in 2 or more columns within each row of sheet, sum the total of all the lo

    Review PQ
    In the file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced

    Also, if you are not familiar with Power Query, there are a couple of links in my signature block. Follow the links to learn more.

+ 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. Replies: 3
    Last Post: 06-26-2019, 07:11 PM
  2. [SOLVED] Find lowest value from criteia and show cell in same row to left 3 columns
    By ANDREAAS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-02-2018, 06:01 PM
  3. [SOLVED] Using VLOOKUP to find the lowest number in two columns
    By tom hatten in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-25-2018, 02:18 PM
  4. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  5. Find each lowest value and return sheet name
    By Astaroths in forum Excel General
    Replies: 3
    Last Post: 04-30-2014, 09:57 AM
  6. Replies: 4
    Last Post: 01-19-2012, 02:51 AM
  7. Find lowest number in groups then lowest overall.
    By swieduwi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2008, 01:00 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