+ Reply to Thread
Results 1 to 8 of 8

Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

  1. #1
    Registered User
    Join Date
    07-16-2016
    Location
    Finta Romania
    MS-Off Ver
    2007
    Posts
    2

    Question Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Hello my friends, i am a basic Excel user, and that's why i need your help. I need a formula to do what i explained in the following picture. I called Microsoft for that :P, maybe i should't, but since i pay for it... Maybe for you is a 1 minute work, but i couldn't handle it.
    Basically, the turnament takes one week. The starting points (F2) is 0, which will grow or decrease depending on the results [H2:N2], the results could be: neutral [the points remain the same (empty cell) lose (0 value in cell) which leeds to droping by 1 point of points value (F2) for each cells from H2:N2 who contains a 0 value and win (value<>0 and also <>Empty cell) which leeds to a grow by 3 for points value,F2] So:Ajutor_forum.png I hope i was clear enough, although i wait for your questions to clarify. Thanks ! :D

  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,003

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Please post a sample Excel file (not image).

    To upload a file click "Go advanced" then scroll down to "Manage Attachments".

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    This solution uses helper columns. The formula for the helper columns, entered in O2 then dragged across to U2, is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula for F2 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me 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.

  5. #5
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Hi,

    With the remark that 3+3+3+0-1=8 (F23), in F2 use this CSE (Ctrl+Shift+Enter)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you have US Regional Settings change ";" with ",".

    see attasament

    PS. Where is Finta? In Dambovita?
    Attached Files Attached Files

  6. #6
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Try this....

    =SUM(COUNTIF(H2:N2,{">0","0"})*{3,-1})

    Normally Enter

  7. #7
    Registered User
    Join Date
    07-16-2016
    Location
    Finta Romania
    MS-Off Ver
    2007
    Posts
    2

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Holy god ! It's frickin' working! I can't belive today i had no formula, and now i can chose one of above, because i have to say, i tried all your solutions and they all worked.
    Please post a sample Excel file (not image).

    To upload a file click "Go advanced" then scroll down to "Manage Attachments".
    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    YEP. Make a lot of sense, what you guys said here. I will do it this way from now on. Very thanks for those advices. ( JohnTopley
    & Tony Valko)

    Quote Originally Posted by JeteMc View Post
    This solution uses helper columns. The formula for the helper columns, entered in O2 then dragged across to U2, is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula for F2 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Working and undersood the way you solved the problem. I didn't knew the syntax for Excel codes... Thank you very much though! JeteMc


    Quote Originally Posted by TudyBTH View Post
    Hi,

    With the remark that 3+3+3+0-1=8 (F23), in F2 use this CSE (Ctrl+Shift+Enter)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you have US Regional Settings change ";" with ",".

    see attasament

    PS. Where is Finta? In Dambovita?
    TudyBTH , the way you solved this it's like JeteMc, i think :D . Your attachament was the first tried by me, and when i saw it was working, i was so happy, i've should recorded my reactions ahahahah ) , even if i know it's not such a big thing, but... was working, and i spent like a half an hour today, maybe more, to find the solution and i am really dissapointed of myself, because i didn't... Probably that's why i was so happy ) And also thans for the advice regarding ~If you have US Regional Settings change ";" with ",". ~ It helped me, because the other 2 formulas posted here didn't worked first time, and the reason was exactly what you said here. And about Finta, yes is in Dambovita, do you know the village, or you just Google it?

    Quote Originally Posted by Teethless mama View Post
    Try this....

    =SUM(COUNTIF(H2:N2,{">0","0"})*{3,-1})

    Normally Enter
    Teethless mama, or "Mama fara dinti" , if you translate your use in my language ) , your formula was the simplest, it's worderful working, but the syntax you used to write it's different from above, and i didn't understand why you didn't set also the condition for a empty cell, or it's there and i am so unwitting that i don't see it :P

    So thank you ALL very much for your help, you made a happy person somewhere in Romania And if you want to know anything else send me a P.M

    For Moderator: We SOLVED this toghether, if you consider you can close this thread.

    Take care of you !
    Last edited by petryk6; 07-16-2016 at 05:18 PM.

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

    Re: Excel SUMIF and SUBSTRACTIF data table (Football Turnament)

    Glad that you got some solutions that work for you, thank you for the feedback. As to marking the thread 'Solved', please do that by using the thread tools link above your first post in the thread. I am glad that your first experience with Excel Forum was successful and I hope that you have a blessed day.

+ 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. Help with football league table
    By liverbb in forum Excel General
    Replies: 8
    Last Post: 09-25-2014, 02:20 PM
  2. sort 40 football league table
    By peterfc2 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2014, 05:43 AM
  3. Football League Table
    By Falconair in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2013, 03:25 PM
  4. football league table
    By rocky123 in forum Excel General
    Replies: 8
    Last Post: 07-24-2009, 11:47 AM
  5. rank for football table
    By plumberman in forum Excel General
    Replies: 10
    Last Post: 07-17-2008, 09:59 AM
  6. Football League Table
    By Crucfier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2005, 05:05 PM

Tags for this Thread

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