# extending or populating values automatically

1. ## 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. ## 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. ## Re: extending or populating values automatically

Originally Posted by Sofistikat
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. ## 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. ## Re: extending or populating values automatically

Originally Posted by Sofistikat
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. ## 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. ## Re: extending or populating values automatically

Originally Posted by Sofistikat
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.

9. ## 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

10. ## Re: extending or populating values automatically

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

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