| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBTOTAL QUERY
Hi All,
Can some help. I am using Excel 2007 I have 3 columns A B C Invoice Amount Sub Total D1000 50.00 D1000 1250.00 D1200 5000.00 D1200 1250.00 D1300 750.00 D1300 999.90 My question is this - how can I using the SUBTOTAL function in Excel,get it to subtotal the total value for each item in Col A and put the subtotal of these occurences in Column C. eg. in Column C for D1000 = 1300.00, D1200 = 6250.00 etc. I have tried every way but I can only get excel to put the subtotal amount in Col B after the end of each occurence of the totalled invoice numbers.Can anyone assist? Many thanks FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUBTOTAL QUERY
Try this in C2:
=IF(C2=C3,"",SUMIF(A:A,A2,B:B)) then copy it down. It should give you subtotals against the last entry of consecutive values in A. This assumes that the data is sorted on column A. Data | Subtotals works in the way you describe, so you can't get the effect you want using that. Hope this helps. Pete On Jul 31, 3:37*pm, Finance Guru wrote: Hi All, Can some help. *I am using Excel 2007 I have 3 columns A * * * * * * * * *B * * * * * * * * C Invoice * * * Amount * * Sub Total D1000 * * * * *50.00 D1000 * * * 1250.00 D1200 * * * 5000.00 D1200 * * * 1250.00 D1300 * * * * 750.00 D1300 * * * * 999.90 My question is this - how can I using the SUBTOTAL function in Excel,get it to subtotal the total value for each item in Col A and put the subtotal of these occurences in Column C. *eg. in Column C for D1000 = 1300.00, D1200 = 6250.00 etc. I have tried every way but I can only get excel to put the subtotal amount in Col B after the end of each occurence of the totalled invoice numbers.Can anyone assist? Many thanks FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUBTOTAL QUERY
Hi Pete,
Thanks for your response. Yes I could do it that way,my question was more whether I was missing anything in the SUBTOTAL ( not shouting ) function to achieve this automatically. Thanks again FinanceGuru -- Wales - Where once miners toiled,now fire breathing dragons and rugby playing wizards play in lush green valleys. "Pete_UK" wrote: Try this in C2: =IF(C2=C3,"",SUMIF(A:A,A2,B:B)) then copy it down. It should give you subtotals against the last entry of consecutive values in A. This assumes that the data is sorted on column A. Data | Subtotals works in the way you describe, so you can't get the effect you want using that. Hope this helps. Pete On Jul 31, 3:37 pm, Finance Guru wrote: Hi All, Can some help. I am using Excel 2007 I have 3 columns A B C Invoice Amount Sub Total D1000 50.00 D1000 1250.00 D1200 5000.00 D1200 1250.00 D1300 750.00 D1300 999.90 My question is this - how can I using the SUBTOTAL function in Excel,get it to subtotal the total value for each item in Col A and put the subtotal of these occurences in Column C. eg. in Column C for D1000 = 1300.00, D1200 = 6250.00 etc. I have tried every way but I can only get excel to put the subtotal amount in Col B after the end of each occurence of the totalled invoice numbers.Can anyone assist? Many thanks FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUBTOTAL QUERY
Well, putting a formula in one cell and copying it down is fairly
automatic, and will certainly be faster than the Data | Subtotal route (which doesn't give you what you want anyway), if you have a lot of invoices ! Pete On Jul 31, 4:27*pm, Finance Guru wrote: Hi Pete, Thanks for your response. Yes I could do it that way,my question was more whether I was missing anything in the SUBTOTAL ( not shouting ) function to achieve this automatically. Thanks again FinanceGuru -- Wales - Where once miners toiled,now fire breathing dragons and rugby playing wizards play in lush green valleys. "Pete_UK" wrote: Try this in C2: =IF(C2=C3,"",SUMIF(A:A,A2,B:B)) then copy it down. It should give you subtotals against the last entry of consecutive values in A. This assumes that the data is sorted on column A. Data | Subtotals works in the way you describe, so you can't get the effect you want using that. Hope this helps. Pete On Jul 31, 3:37 pm, Finance Guru wrote: Hi All, Can some help. *I am using Excel 2007 I have 3 columns A * * * * * * * * *B * * * * * * * * C Invoice * * * Amount * * Sub Total D1000 * * * * *50.00 D1000 * * * 1250.00 D1200 * * * 5000.00 D1200 * * * 1250.00 D1300 * * * * 750.00 D1300 * * * * 999.90 My question is this - how can I using the SUBTOTAL function in Excel,get it to subtotal the total value for each item in Col A and put the subtotal of these occurences in Column C. *eg. in Column C for D1000 = 1300.00, D1200 = 6250.00 etc. I have tried every way but I can only get excel to put the subtotal amount in Col B after the end of each occurence of the totalled invoice numbers.Can anyone assist? Many thanks FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards.- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUBTOTAL QUERY
I agree. Thanks again for your help
-- Wales - Can it really be the land of fire breathing dragons and rugby playing wizards. Come on down and see for oneself. "Pete_UK" wrote: Well, putting a formula in one cell and copying it down is fairly automatic, and will certainly be faster than the Data | Subtotal route (which doesn't give you what you want anyway), if you have a lot of invoices ! Pete On Jul 31, 4:27 pm, Finance Guru wrote: Hi Pete, Thanks for your response. Yes I could do it that way,my question was more whether I was missing anything in the SUBTOTAL ( not shouting ) function to achieve this automatically. Thanks again FinanceGuru -- Wales - Where once miners toiled,now fire breathing dragons and rugby playing wizards play in lush green valleys. "Pete_UK" wrote: Try this in C2: =IF(C2=C3,"",SUMIF(A:A,A2,B:B)) then copy it down. It should give you subtotals against the last entry of consecutive values in A. This assumes that the data is sorted on column A. Data | Subtotals works in the way you describe, so you can't get the effect you want using that. Hope this helps. Pete On Jul 31, 3:37 pm, Finance Guru wrote: Hi All, Can some help. I am using Excel 2007 I have 3 columns A B C Invoice Amount Sub Total D1000 50.00 D1000 1250.00 D1200 5000.00 D1200 1250.00 D1300 750.00 D1300 999.90 My question is this - how can I using the SUBTOTAL function in Excel,get it to subtotal the total value for each item in Col A and put the subtotal of these occurences in Column C. eg. in Column C for D1000 = 1300.00, D1200 = 6250.00 etc. I have tried every way but I can only get excel to put the subtotal amount in Col B after the end of each occurence of the totalled invoice numbers.Can anyone assist? Many thanks FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards.- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUBTOTAL QUERY
You're welcome.
Pete On Jul 31, 5:04*pm, Finance Guru wrote: I agree. Thanks again for your help -- Wales - Can it really be the land of fire breathing dragons and rugby playing wizards. Come on down and see for oneself. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro excel subtotal in subtotal | Excel Discussion (Misc queries) | |||
| pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
| Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
| copy subtotal value only, subtotal value can be vlookup by others | Excel Discussion (Misc queries) | |||
| Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Hybrid Mode
