Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotals
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 [...] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |