subtotals

[Timo]

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
[...]

 

[Pete_UK]

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

 

[Timo]

Hi Pete,

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

Cheers,

Timo

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

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
[...]

 

[Jim Thomlinson]

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

Hi Pete,

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

Cheers,

Timo

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

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
[...]

 

[Eduardo]

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

=subtotal(9,C1:c100)

then filter your information by name

 

[Timo]

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.

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

=subtotal(9,C1:c100)

then filter your information by name

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
[...]

 

[Timo]

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.

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

Hi Pete,

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

Cheers,

Timo

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

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
[...]

 

[Eduardo]

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

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.

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

=subtotal(9,C1:c100)

then filter your information by name

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
[...]

 

[Eduardo]

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

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.

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

=subtotal(9,C1:c100)

then filter your information by name

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
[...]

 

[Timo]

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

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

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.

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

=subtotal(9,C1:c100)

then filter your information by name

:

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
[...]

 

[Jim Thomlinson]

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

Jim Thomlinson

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.

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

Hi Pete,

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

Cheers,

Timo

:

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

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
[...]

 

[Eduardo]

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

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

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

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.

:

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

=subtotal(9,C1:c100)

then filter your information by name

:

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
[...]

 

[Pete_UK]

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

Pete

Hi Pete,

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

Cheers,

Timo

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

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 theID 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 -

 

[Timo]

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

Timo

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

Pete

Hi Pete,

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

Cheers,

Timo

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.

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 -