Forum Discussion

User537843's avatar
User537843
Copper Contributor
May 31, 2023

Adding cells and splitting info

Hello everyone, this question is about totaling. I would like to total the numbers in F1, and L1, and every 6th column after that, so the next is R1. I don’t want the whole number totaled, instead, I need each number position totaled separately. It will be split and totaled in A2, through A7. For example, A2 is a total of F1 - position #1, which is “1”, plus L1 - position #1, which is “5”, so A2 = 6. What formula can automate this process across thousands of cells? The formula would need to ignore the cells inbetween every 6th cell. Thank you.

 

Formulas and Functions

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 31, 2023

    User537843 

    Sub total()

Dim h, i, j, k, m As Long
Range("A:A").Clear

m = 1
For h = 1 To 6
    For i = 6 To 16380 Step 6
    j = Mid(Cells(1, i), m, 1)
    k = WorksheetFunction.NumberValue(j, ".", ";") + k
    Next i
    Cells(h + 1, 1).Value = k
    k = 0
    j = 0
    m = m + 1
Next h

End Sub

    An alternative could be these lines of code.

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 31, 2023

    something like
    =SUM(IFERROR(--LEFT(CHOOSECOLS(F1:ZZ1,SEQUENCE(INT((COLUMNS(F1:ZZ1)+5)/6),,,6)),1),0))

    EDIT - I now see you need each digit added:

    =BYROW(IFERROR(--MID(TEXT(CHOOSECOLS(F1:ZZ1,SEQUENCE(INT((COLUMNS(F1:ZZ1)+5)/6),,,6)),"000000"),{1;2;3;4;5;6},1),0),LAMBDA(r,SUM(r)))

    note this also adds a condition to account for numbers that might have leading 0 

Resources