+ Reply to Thread
Results 1 to 7 of 7

How to create a formula that finds the sum of points for teams

  1. #1
    Registered User
    Join Date
    10-03-2014
    Location
    Edmonton, Alberta
    MS-Off Ver
    Office 2013
    Posts
    2

    How to create a formula that finds the sum of points for teams

    Hello!

    Just discovered this forum today, but cannot find the answer. Can any of you help me?

    I have uploaded a file. As you will see, it is a simple array of team names with their rankings - 10 points, then 9 points, etc. What I want to calculate in the yellow cell is the total number of points associated with, in that case, the Ducks. The answer should be 49, as the formula would add up everything in cells E2, E3, E4, E5, and G7.

    Can it be done? Any suggestions? I've gone around from IF statements, to VLOOKUPS, to them together, to SUMIFs, and on and on. It should be simpler than that!

    Thanks in advance!

    Melody
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: How to create a formula that finds the sum of points for teams

    Hi, welcome to the forum

    Try this...
    =SUM(COUNTIF($D$2:$D$7,$A2)*10,COUNTIF($F$2:$F$7,$A2)*9,COUNTIF($H$2:$H$7,$A2)*8,COUNTIF($J$2:$J$7,$A2)*7,COUNTIF($L$2:$L$7,$A2)*6,COUNTIF($N$2:$N$7,$A2)*5,COUNTIF($P$2:$P$7,$A2)*4,COUNTIF($R$2:$R$7,$A2)*3,COUNTIF($T$2:$T$7,$A2)*2,COUNTIF($V$2:$V$7,$A2))

    Im sure someone will come up with a more eligant answer (and I will keep looking too)

    edit: Could you have your data in a straight table of 2 columns? That would make things a ton easier....
    =SUMIFS($E$2:$E$61,$D$2:$D$61,A2)
    Last edited by FDibbins; 10-03-2014 at 07:33 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-03-2014
    Location
    Edmonton, Alberta
    MS-Off Ver
    Office 2013
    Posts
    2

    Smile Re: How to create a formula that finds the sum of points for teams

    Beautiful! Thanks so very much for this!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: How to create a formula that finds the sum of points for teams

    Glad it worked for you and thanks for the feedback

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to create a formula that finds the sum of points for teams

    Try this:


    Enter in B2 and copy down.


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: How to create a formula that finds the sum of points for teams

    Thanks Ron, I didnt think that would work, so I didnt try it. It also works with just sumif()...
    =SUMIF($D$2:$V$7,A2,$E$2:$W$7)

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to create a formula that finds the sum of points for teams

    Thanks for the reminder Ford......habits, habits....LOL

+ 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. Ranking teams in order based on points
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2014, 11:41 AM
  2. Replies: 1
    Last Post: 05-14-2008, 02:59 PM
  3. [SOLVED] how to set up Bowls league 12 teams points and shot difference
    By Mel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] how to set up Bowls league 12 teams points and shot difference
    By Mel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-07-2005, 08:05 AM
  5. [SOLVED] How do I create a formula in Excel that finds the value of the la.
    By BerlinUSA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2005, 09:06 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