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
|