+ Reply to Thread
Results 1 to 10 of 10

extending or populating values automatically

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    extending or populating values automatically

    Hello,

    I want to automatically populate values of certain cells but it never takes the right formulae when I extend the "+" sign next to the cell with the formula.
    Say I have 2 column entries as below:
    A B
    John 1
    John 0
    John 1
    Bill 2
    Bill 1
    Bill 1
    Jack 3
    Jack 2
    Jack 1
    Jill 4
    Jill 4
    Jill 1

    Now, when i do SUM(B2:B4), it returns 2. Similarly, I do SUM(B5:B7) below to get 4. Now I would like to pull down (using that '+' sign) to get 6, 9 etc.....
    Instead it takes other formula and gives me the wrong answer. Please tell me if there's an easy way to do this instead of calculating for each cell separately.

    Thanks,
    KF

  2. #2
    Registered User
    Join Date
    10-10-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, 2007 & 2010
    Posts
    88

    Re: extending or populating values automatically

    Do you only want to get totals per person? That is, total for john, total for Bill etc?

  3. #3
    Registered User
    Join Date
    09-21-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: extending or populating values automatically

    Quote Originally Posted by Sofistikat View Post
    Do you only want to get totals per person? That is, total for john, total for Bill etc?
    Yes. But I have a lot of data in my actual excel sheet and so the total would help.
    Thanks,
    KF

  4. #4
    Registered User
    Join Date
    10-10-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, 2007 & 2010
    Posts
    88

    Re: extending or populating values automatically

    No problem, try this:

    =SUMIF(A1:A5000,"John",B1:B5000)

    A5000 & B5000 can be extended to cover however many records you have in your columns if required, and "John" can be changed to whatever other name you like, or you could state this formula several times in different cells, each with their own specific name.

    Hope this helps.

  5. #5
    Registered User
    Join Date
    09-21-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: extending or populating values automatically

    Quote Originally Posted by Sofistikat View Post
    No problem, try this:

    =SUMIF(A1:A5000,"John",B1:B5000)

    A5000 & B5000 can be extended to cover however many records you have in your columns if required, and "John" can be changed to whatever other name you like, or you could state this formula several times in different cells, each with their own specific name.

    Hope this helps.
    Thanks Sofistikat. Yes, it did teach me another way of summing.
    But this still takes time to write the formula for every John, Bill, Jack etc.
    I could have also done =SUM(B2:B4), =SUM(B5:B7) etc.
    Since I have many sets of John, Bill etc, I would prefer some formula to write just once and pull down for it to auto populate i.e. to write a formula to add John's total and pull down to automatically get Bill's, Jack's etc totals. I have the same number of iterations for each person i.e. in this example 3 of each.
    In short, if I could write =SUM(B2:B4) and pull down on the plus sign which would autopopulate with these formulae in each bottom cell i.e. =SUM(B5:B7), =SUM(B8:B10) etc.
    Thanks,
    KF

  6. #6
    Registered User
    Join Date
    10-10-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, 2007 & 2010
    Posts
    88

    Re: extending or populating values automatically

    If you make a list of all the people you need to get totals for starting from Cell E1 for example, as follows:

    Cell E1 = John
    Cell E2 = Bill
    Cell E3 = Jack
    Cell E4 = Jill

    The you could put the following formula into Cell F1 and copy down:

    =SUMIF($A$1:$A$5000,E1,$B$1:$B$5000)

  7. #7
    Registered User
    Join Date
    09-21-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: extending or populating values automatically

    Quote Originally Posted by Sofistikat View Post
    If you make a list of all the people you need to get totals for starting from Cell E1 for example, as follows:

    Cell E1 = John
    Cell E2 = Bill
    Cell E3 = Jack
    Cell E4 = Jill

    The you could put the following formula into Cell F1 and copy down:

    =SUMIF($A$1:$A$5000,E1,$B$1:$B$5000)
    Interesting. But what if I have it sorted out already with John in cell E1:E3, Bill in Cell E4:E6 with their respective values in F1:F3, F4:F6 etc.

  8. #8
    Registered User
    Join Date
    10-10-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003, 2007 & 2010
    Posts
    88

    Re: extending or populating values automatically

    Won't make any difference. It will still return totals for all your people, whether your data is sorted or not.

  9. #9
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: extending or populating values automatically

    Maybe this is what you are looking for:

    Please Login or Register  to view this content.
    It will give you the sum of the corresponding numbers next to each person.

    Hope this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,289

    Re: extending or populating values automatically

    Try this formula in C2 and fill down:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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