subtotals

T

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

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
 
T

Timo

Hi Pete,

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

Cheers,

Timo

Pete_UK said:
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
[...]
 
J

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


Timo said:
Hi Pete,

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

Cheers,

Timo

Pete_UK said:
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
[...]
 
E

Eduardo

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

=subtotal(9,C1:c100)

then filter your information by name
 
T

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.

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

=subtotal(9,C1:c100)

then filter your information by name

Timo said:
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
[...]
 
T

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.

Jim Thomlinson said:
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 said:
Hi Pete,

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

Cheers,

Timo

Pete_UK said:
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
[...]
 
E

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

Timo said:
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 said:
Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

Timo said:
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
[...]
 
E

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

Timo said:
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 said:
Hi,
at the bottom or top of your sheets enter this formula

=subtotal(9,C1:c100)

then filter your information by name

Timo said:
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
[...]
 
T

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

Eduardo said:
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 said:
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 said:
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
[...]
 
J

Jim Thomlinson

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

Jim Thomlinson


Timo said:
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 said:
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 said:
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
[...]
 
E

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

Timo said:
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 said:
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 said:
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
[...]
 
P

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



Pete_UK said:
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 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 -
 
T

Timo

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

Timo

Pete_UK said:
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



Pete_UK said:
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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

data merge 2
Matching Values of 1 row vs all other rows. 0
Comparing two lists in Excel 1
lookup without repeats 3
Pivot Table Formatting 1
Sum with conditions 1
Insert Rows 1
How to merge to coloums 1

Top