Forum Discussion

pbakseli's avatar
pbakseli
Occasional Reader
Nov 25, 2025

How to sum chain values from N-number cells?

Hello,

 

I have data of people by age, but 100 different values on a line chart would look... bad. Hence the need to group ages in sets of 5 or 10 on a separe line in chain, but what is the function to do this? It certainly isn't the humble SUM function, since it moves the range by one, producing "1 the problem".

Sure, I could do the SUM chain and delete four cells between keepers, yielding "2 the problem", but that's just awful. 

Or I could do "WHAT I SEEK" manually, if I had an enernity... 


Thanks in advance, and have a wonderful day!

 

 

Charting
excel
Formulas and Functions

2 Replies

  • IlirU's avatar
    IlirU
    Brass Contributor
    Nov 25, 2025

     

    Hi,

     

    (see the screenshot above)

     

    In cell B4 apply below formula:

    =TOCOL(EXPAND(TOCOL(BYROW(WRAPROWS(B4:B24, 5), SUM), 3),, 5, ""))

     

    In cell C4 apply below formula:

    =LET(wr, WRAPROWS(A4:A24, 5), TOCOL(EXPAND(TOCOL(BYROW(wr, MIN), 3),, 5, "")) & "-" & TOCOL(EXPAND(TOCOL(BYROW(wr, MAX), 3),, 5, "")))

     

    Hope this helps.

     

    Regards,

    IlirU

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 25, 2025

    This formula is in cell C2 and filled down:

    =IF(MOD(A2,5)=0,SUM(OFFSET(B2,0,0,5)),"")

    This formula is in cell D2 and filled down:

    =IF(MOD(A2,5)=0,A2&"-"&A2+4,"")

    Does this return the intended output?

Resources