+ Reply to Thread
Results 1 to 10 of 10

SUMIF or SUMIFS in a table

  1. #1
    Registered User
    Join Date
    01-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    19

    SUMIF or SUMIFS in a table

    Hi everyone,

    I'm building an Excel sheet for dummies for a fishing club. People that are going to work with this are not used to working with a computer.
    Some matches are in a competition form over multiple matches.

    In the supplied example I would like the column total points and total weight to have no value until all four matches(points and weight) or at least 1 of the 4 matches are filled in.

    Also if possible with a formula or in another way, I would like to leave out the highest number of points and also corresponding weight over 4 matches in the total results.
    Highest points are for the worst result in terms of weight.

    Thanks for the help.
    Attached Files Attached Files
    Last edited by dude1986; 01-20-2018 at 06:24 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF or SUMIFS in a table

    Adding sample data might help.

  3. #3
    Registered User
    Join Date
    01-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    19

    Re: SUMIF or SUMIFS in a table

    New sheet added with some entries

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF or SUMIFS in a table

    By default, the second condition must be included if all results must be present.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF or SUMIFS in a table

    What if we have same points (max) but different weights: do we take highest weight?

  6. #6
    Registered User
    Join Date
    01-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    19

    Re: SUMIF or SUMIFS in a table

    Yes that's correct the weigth is taken into account when the points are equal.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: SUMIF or SUMIFS in a table

    I am not sure that I understand correctly
    If you want "total points and total weight to have no value until all four matches(points and weight)" then you could use the following formulas
    For K2 and down: =IF(J2=0,"",SUM(D2,F2,H2,J2))
    For L2 and down: =IF(I2=0,"",SUM(C2,E2,G2,I2))
    The above assume that points and weights will be filled from left to right.
    On the other hand if you want "total points and total weight to have no value until ...at least 1 of the 4 matches are filled in" then you could use the following formulas
    For K2 and down: =IF(D2=0,"",SUM(D2,F2,H2,J2))
    For L2 and down: =IF(C2=0,"",SUM(C2,E2,G2,I2))
    The above again assume that points and weights will be filled from left to right.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    01-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    19

    Re: SUMIF or SUMIFS in a table

    JeteMc thanks for the help this is just what i need for the totals.

    Now the only thing I would like to know if it's possible formula wise to leave out one result in the totals.
    People fish 4 matches and get point for each match. Winner gets 1 point number 2 gets 2 and so on.
    So highest weight gets lowest points.
    People can leave out the worst result at the end.

    Is this possible with a formula?

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: SUMIF or SUMIFS in a table

    As for points, after the SUM(D2,F2,H2,J2) put -MAX(D2,F2,H2,J2))
    As for weight, after the SUM(... put -MIN(...
    I am “away” for the week, however I hope one of the other contributors can help if needed.

  10. #10
    Registered User
    Join Date
    01-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    19

    Re: SUMIF or SUMIFS in a table

    Thnx this was exactly what I needed.

    You rock!!!

+ 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. [SOLVED] Help with SUMIF or SUMIFS
    By asghar32 in forum Excel General
    Replies: 6
    Last Post: 07-26-2017, 01:23 PM
  2. Help with IF, SUMIF or SUMIFS for full table
    By howieuk1878 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2016, 11:24 AM
  3. sumif()- or sumifs()?
    By szeconku in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2015, 04:11 AM
  4. [SOLVED] SUMIF or SUMIFS to find text and date between from items in a table
    By Beefy1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 10:38 PM
  5. [SOLVED] Sumif/Sumifs
    By kimsem in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2014, 09:37 PM
  6. Excel 2007 : Nested Sumif or Using SUMIFS????
    By MMLBaylor in forum Excel General
    Replies: 5
    Last Post: 03-15-2011, 02:16 PM
  7. Sumifs Or Sumif?
    By Carp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2007, 03:27 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