Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30*pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID * * *Name * *amount
1234 * *Name1 * 100
1234 * *Name1 * 200
2000 * *Name2 * 125
2000 * *Name2 * 200
[...]

Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo

"Pete_UK" wrote:

Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

2 other possibilities...

1 - Add a new column and create a concatenated field of both the ID and the
Name. Use this new field as the basis of the subtotal

2 - Use a pivot table. Add both the name and ID to the left ahnd column and
the amaounts to the data section (this would be my prefered method).
--
HTH...

Jim Thomlinson


"Timo" wrote:

Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo

"Pete_UK" wrote:

Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Hi Eduardo,

sorry, but I don't get this one, in which cell, in relation to the example
to you enter your formula. I get the total 625, but this does not really help.

Thanks.

"Eduardo" wrote:

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Hi Jim,

1) first option is not good, I need to use autofilter afterwards, so the ID
needs to remain in its column.

2) what is a pivot table? Can you give more details, as I would like to try
your option at least.

Thanks.

"Jim Thomlinson" wrote:

2 other possibilities...

1 - Add a new column and create a concatenated field of both the ID and the
Name. Use this new field as the basis of the subtotal

2 - Use a pivot table. Add both the name and ID to the left ahnd column and
the amaounts to the data section (this would be my prefered method).
--
HTH...

Jim Thomlinson


"Timo" wrote:

Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo

"Pete_UK" wrote:

Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Hi Timo,
I assume your amounts are in column C from C1 to C100, if you don't have any
filter the formula will show the sum of that cells C1 to C100, when you
filter the information the formula will calculate the sum of the visible
cells only
if you don't understand what I meant please let me know and I will try again
with an exam-ple

"Timo" wrote:

Hi Eduardo,

sorry, but I don't get this one, in which cell, in relation to the example
to you enter your formula. I get the total 625, but this does not really help.

Thanks.

"Eduardo" wrote:

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

opps I didn't answer your specific question, usually I prefer to have that
formula at the top of the column let's say in row1, then leave next row empty
and start your information from row 3, in that way when you apply the filter
you will be able to see the totals at the top

"Timo" wrote:

Hi Eduardo,

sorry, but I don't get this one, in which cell, in relation to the example
to you enter your formula. I get the total 625, but this does not really help.

Thanks.

"Eduardo" wrote:

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

What does your formula do? when I get the subtotals, I am missing the ID in
the total line. So I would need to get this information. Do the subtotals in
"data" once with my example, than you know what I mean. You will not see any
data from column A. Pete_UK could help, however it takes some time to do it.

Timo

"Eduardo" wrote:

Hi Timo,
I assume your amounts are in column C from C1 to C100, if you don't have any
filter the formula will show the sum of that cells C1 to C100, when you
filter the information the formula will calculate the sum of the visible
cells only
if you don't understand what I meant please let me know and I will try again
with an exam-ple

"Timo" wrote:

Hi Eduardo,

sorry, but I don't get this one, in which cell, in relation to the example
to you enter your formula. I get the total 625, but this does not really help.

Thanks.

"Eduardo" wrote:

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

http://www.peltiertech.com/Excel/Pivots/pivottables.htm
--
HTH...

Jim Thomlinson


"Timo" wrote:

Hi Jim,

1) first option is not good, I need to use autofilter afterwards, so the ID
needs to remain in its column.

2) what is a pivot table? Can you give more details, as I would like to try
your option at least.

Thanks.

"Jim Thomlinson" wrote:

2 other possibilities...

1 - Add a new column and create a concatenated field of both the ID and the
Name. Use this new field as the basis of the subtotal

2 - Use a pivot table. Add both the name and ID to the left ahnd column and
the amaounts to the data section (this would be my prefered method).
--
HTH...

Jim Thomlinson


"Timo" wrote:

Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo

"Pete_UK" wrote:

Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Ok, let's say your information starts in row 3, A3 has ID number, B3 user
name and C3 amounts, then in row 4 you have all the information

in cell c1 enter the formula given before, then highlight the titles in row
3, and enter filters, then press in the arrow from the user header and select
the one you want to see the information, now you can see the ID and amounts
for the user choosen, the formula will show the total amount for that user,
if then you choose another user, the subtotal will automatically change

"Timo" wrote:

What does your formula do? when I get the subtotals, I am missing the ID in
the total line. So I would need to get this information. Do the subtotals in
"data" once with my example, than you know what I mean. You will not see any
data from column A. Pete_UK could help, however it takes some time to do it.

Timo

"Eduardo" wrote:

Hi Timo,
I assume your amounts are in column C from C1 to C100, if you don't have any
filter the formula will show the sum of that cells C1 to C100, when you
filter the information the formula will calculate the sum of the visible
cells only
if you don't understand what I meant please let me know and I will try again
with an exam-ple

"Timo" wrote:

Hi Eduardo,

sorry, but I don't get this one, in which cell, in relation to the example
to you enter your formula. I get the total 625, but this does not really help.

Thanks.

"Eduardo" wrote:

Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

"Timo" wrote:

Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]

Well, thanks for feeding back. I see you've had other responses since
I posted.

Pete

On Aug 25, 3:01*pm, Timo wrote:
Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo



"Pete_UK" wrote:
Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID * * *Name * *amount
1234 * *Name1 * 100
1234 * *Name1 * 200
2000 * *Name2 * 125
2000 * *Name2 * 200
[...]- Hide quoted text -

- Show quoted text -

Yes, but I don't really get them. Your advice helped a lot.

Timo

"Pete_UK" wrote:

Well, thanks for feeding back. I see you've had other responses since
I posted.

Pete

On Aug 25, 3:01 pm, Timo wrote:
Hi Pete,

thanks, nice one, even though it takes a few steps...no other possibility?

Cheers,

Timo



"Pete_UK" wrote:
Highlight the ID column and then press F5 (GoTo), click on Special,
then on Blanks. Then begin to enter a formula by typing =, then click
on the cell immediately above the first blank (it will be the last one
that has 1234 as an ID in your example), and then do CTRL-Enter. All
the blank cells will be filled with a formula which will copy the ID
from the line above.

Hope this helps.

Pete

On Aug 25, 2:30 pm, Timo wrote:
Dear all,

I want to use subtotals for Name, to sum the amounts. I would get a total
line for Name1 with value 300... However I would also like to see the ID in
the total line. How do I do that? Thanks in advance.

ID Name amount
1234 Name1 100
1234 Name1 200
2000 Name2 125
2000 Name2 200
[...]- Hide quoted text -

- Show quoted text -