adding subTotals from a Column

[stew]

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

 

[John C]

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.

 

[stew]

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]

Well, your formula as stated you could try:
=SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),113)=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

 

[stew]

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),103)=111))

tHANKS

sTEW

 

[stew]

Dear John

Got It

=SUMPRODUCT((C111:C25655)*(MOD(ROW(C111:C25655),103)=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

 

[John C]

Glad to help, and thanks for the feedback.