+ Reply to Thread
Results 1 to 6 of 6

Create a cumulative sum within a category

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    22

    Create a cumulative sum within a category

    Hi guys,


    Have been on the drawing board with this for a while, seems like a really simple task but I just cant crack it, got to the stage where the fun problem solving aspect is starting to give way to deadlines!

    The data is on the NFL, I have information on each match played and the individual players movements within each match, I am looking to sum information from each previous match so I have each players total at any point in time.

    A file with dummy data is attached, the outcome I am looking for is to have the Total to date column work out how many yards each player has had up to that point in time, for example the player with the ID 5486 had 6 yards in 2009, hence the Total to date column would contain a 6. They then went on to make 11 yards in 2010, so the column Total to date should display 17 (11+6). However this obviously needs to differentiate between the different players.


    NFL dummy data.xlsx

    Tried all sorts of combinations of IF's, and a few Index Matches, the latter seems to be the most promising! Any ideas guys?

    Thanks!

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,547

    Re: Create a cumulative sum within a category

    =SUMIF(A$2:A2,A2,C$2:C2)
    Try this formula in Cell D2 and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,547

    Re: Create a cumulative sum within a category

    Ignore my earlier post and try the below one
    D2=SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,"<="&B2)
    and copy towards down

  4. #4
    Registered User
    Join Date
    10-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Create a cumulative sum within a category

    Thanks! This worked in the example spreadsheet but then not in my actual dataset, i've attached the actual dataset but can see no reason why its not exactly the same?

    Also, for anyone else using this post, this needs to be sorted by date right?

    Just noticed your username, NFL analyst?
    OFFENCE NFL.xlsx

    Thanks,

  5. #5
    Registered User
    Join Date
    10-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Create a cumulative sum within a category

    Sorted, some of my years had changed to text and were not recognised. Thanks for this, really helpful reply!

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,547

    Re: Create a cumulative sum within a category

    thanks for your feedback

+ 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. How to create a cumulative SUMIF?
    By makkystyle in forum Excel General
    Replies: 10
    Last Post: 04-14-2020, 01:56 PM
  2. How to create own category for my udf's?
    By vbfortran in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2008, 06:23 AM
  3. [SOLVED] How do I create a more than cumulative frequency polygon ?
    By yahoo in forum Excel General
    Replies: 1
    Last Post: 05-22-2006, 03:15 PM
  4. How do you create a cumulative line graph?
    By Ray in forum Excel General
    Replies: 4
    Last Post: 12-04-2005, 07:15 PM
  5. drag value to create cumulative numbering
    By wilsdo in forum Excel General
    Replies: 0
    Last Post: 05-16-2005, 09:25 AM

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