| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding subTotals from a Column
Hi
A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc
(note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ....etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
Thank you John. This would make it easy if I was Just starting this sheet,
However it exists, and I am trying to make it easier if Possible to Fulfil the direction. Thanks for looking Stew "John C" wrote: If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc (note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ...etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
Well, your formula as stated you could try:
=SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),11 3)=111)) However, your last cell reference, C25655, is not going to be summed. Taking your cue of starting in C111, going every 113 rows, you would come out like: C111, C224, C337, ..., C25536, C25649 so you may want to check your cell references Essentially, your first parameter will be your entire range, as will the row reference. The MOD function divides each row number by that number (your case, 113 rows between each), and if the remainder is 111 (111/113 = 0 r 111, 224/113 = 1 r 111, etc.), then it will sum that value. If you have further clarification on your dataset, post it :) -- John C "stew" wrote: Thank you John. This would make it easy if I was Just starting this sheet, However it exists, and I am trying to make it easier if Possible to Fulfil the direction. Thanks for looking Stew "John C" wrote: If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc (note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ...etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
Dear John Thanks for bearing with me. I have now checked . C111 IS THE FIRST SUB TOTAL AND THERE AFTER , C214,C317,420,523 etc. 103 rows jump and not, as i originally stated , 113. ENDING ON 25655 25655-111=25544. 25544/103= 248 Does That then make the Formula in C5 =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),10 3)=111)) tHANKS sTEW "John C" wrote: Well, your formula as stated you could try: =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),11 3)=111)) However, your last cell reference, C25655, is not going to be summed. Taking your cue of starting in C111, going every 113 rows, you would come out like: C111, C224, C337, ..., C25536, C25649 so you may want to check your cell references Essentially, your first parameter will be your entire range, as will the row reference. The MOD function divides each row number by that number (your case, 113 rows between each), and if the remainder is 111 (111/113 = 0 r 111, 224/113 = 1 r 111, etc.), then it will sum that value. If you have further clarification on your dataset, post it :) -- John C "stew" wrote: Thank you John. This would make it easy if I was Just starting this sheet, However it exists, and I am trying to make it easier if Possible to Fulfil the direction. Thanks for looking Stew "John C" wrote: If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc (note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ...etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
Dear John Got It =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),10 3)=8)) Thank you so much for being Patient. Now I have learned another Function. This is the best hobby I have ever had. So much to learn Thanks Stew "stew" wrote: Dear John Thanks for bearing with me. I have now checked . C111 IS THE FIRST SUB TOTAL AND THERE AFTER , C214,C317,420,523 etc. 103 rows jump and not, as i originally stated , 113. ENDING ON 25655 25655-111=25544. 25544/103= 248 Does That then make the Formula in C5 =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),10 3)=111)) tHANKS sTEW "John C" wrote: Well, your formula as stated you could try: =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),11 3)=111)) However, your last cell reference, C25655, is not going to be summed. Taking your cue of starting in C111, going every 113 rows, you would come out like: C111, C224, C337, ..., C25536, C25649 so you may want to check your cell references Essentially, your first parameter will be your entire range, as will the row reference. The MOD function divides each row number by that number (your case, 113 rows between each), and if the remainder is 111 (111/113 = 0 r 111, 224/113 = 1 r 111, etc.), then it will sum that value. If you have further clarification on your dataset, post it :) -- John C "stew" wrote: Thank you John. This would make it easy if I was Just starting this sheet, However it exists, and I am trying to make it easier if Possible to Fulfil the direction. Thanks for looking Stew "John C" wrote: If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc (note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ...etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding subTotals from a Column
Glad to help, and thanks for the feedback.
-- John C "stew" wrote: Dear John Got It =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),10 3)=8)) Thank you so much for being Patient. Now I have learned another Function. This is the best hobby I have ever had. So much to learn Thanks Stew "stew" wrote: Dear John Thanks for bearing with me. I have now checked . C111 IS THE FIRST SUB TOTAL AND THERE AFTER , C214,C317,420,523 etc. 103 rows jump and not, as i originally stated , 113. ENDING ON 25655 25655-111=25544. 25544/103= 248 Does That then make the Formula in C5 =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),10 3)=111)) tHANKS sTEW "John C" wrote: Well, your formula as stated you could try: =SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),11 3)=111)) However, your last cell reference, C25655, is not going to be summed. Taking your cue of starting in C111, going every 113 rows, you would come out like: C111, C224, C337, ..., C25536, C25649 so you may want to check your cell references Essentially, your first parameter will be your entire range, as will the row reference. The MOD function divides each row number by that number (your case, 113 rows between each), and if the remainder is 111 (111/113 = 0 r 111, 224/113 = 1 r 111, etc.), then it will sum that value. If you have further clarification on your dataset, post it :) -- John C "stew" wrote: Thank you John. This would make it easy if I was Just starting this sheet, However it exists, and I am trying to make it easier if Possible to Fulfil the direction. Thanks for looking Stew "John C" wrote: If you are actually using the SUBTOTAL calculations in C111, C214, C327, etc (note, you actually have the first 2 references 103 apart 111 to 214, not 113 apart as stated and shown by 214 to 327), then you can still use SUBTOTAL: i.e.: values in C11-C110 are to be 'added' up in C111 C111: =SUBTOTAL(9,C11:C110) values C114-C213 are to be added up in C214 C214: =SUBTOTAL(9,C114:C213) C327: =SUBTOTAL(9,C227:C326) ...etc. C25565: =SUBTOTAL(9,C25465:C25564) Then your formula in C5 could be =SUBTOTAL(9,C11:C25565) Note: You may want to read up on the SUBTOTAL function, the first parameter is how you want to add it up, 9 means SUM. Hope this helps. -- John C "stew" wrote: Hi A simple one for you all Total In C5 Made up of Sub Totals in C111,C214,C327 etc, always 113 apart to a final location of C25655 Can you give the smart Formula that allows me to add these subtotals Thanks for looking Stew |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding subtotals from several worksheets | Excel Worksheet Functions | |||
| Multiple column headings with Subtotals | Excel Discussion (Misc queries) | |||
| Subtracting two Subtotals in the same column | Excel Discussion (Misc queries) | |||
| Adding columns w/new formulas BUT keeping subtotals | Excel Discussion (Misc queries) | |||
| Subtotals adding a description next to the subtotals | Excel Worksheet Functions | |||
Linear Mode
