I know how to sum all digits in a cell using the following formula:=SUMPRODUCT(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)) But how do I do this for a range of cells oriented left to right not vertically? I want the output to be the row under the data not beside it. Please see attached screenshot.

I want to sum the numbers in cell B2, C2, D2, E2 and G2 so that is 7, 4, 5, 6, 9 and 9. And then I want to add those six numbers together. So that is two outputs for each main row for a spreadsheet that goes back to 2017. I have already done it manually in the attached screenshot but I am wondering if there is a macro I can use.

Thanks!

I tried doing this manually for each main row but it is time consuming. There has to be a better way.

CodePudding user response：

1. Click in cell H2
2. Type in =Sum(B2:G2)
3. Press ENTER
4. Click on H2 again
5. Drag the little black box on the bottom right of the cell down to copy the formula for all the rows.

CodePudding user response：

You can try this VBA code:

Sub AddSum()

Dim sumCellNumber As Long
sumCellNumber = 8

For rw = 2 To ActiveSheet.UsedRange.Rows.Count      ' Skip headers
    If ActiveSheet.Cells(rw, 1).Value <> "" Then    ' Date cell
        ActiveSheet.Cells(rw, sumCellNumber).Formula = "=SUM(B" & rw & ":G" & rw & ")" ' Set formula to sum the colums
        If sumCellNumber = 8 Then ' Change the position of the sum cell and background color
            ActiveSheet.Cells(rw, sumCellNumber).Interior.ColorIndex = 17 ' Result in H column
            sumCellNumber = 9
        Else
            ActiveSheet.Cells(rw, sumCellNumber).Interior.ColorIndex = 27 ' Result in I column
            sumCellNumber = 8
        End If
    End If
Next rw

End Sub

What it does is:

• Get all used rows from the active worksheet
• If the date column is not empty
• Then sets the SUM formula that adds the numbers from B to G column
• The result is shown in either H or I column using the background

  Formula in A2:

  =MAP(A1:F1,LAMBDA(x,SUM(--MID(x,SEQUENCE(LEN(x)),1))))
  

  If you have multiple rows that you need to squeeze these rows inbetween, then try:

  

  Formula in I1:

  =LET(x,FILTER(A:G,A:A<>""),REDUCE(TAKE(x,1),SEQUENCE(ROWS(x)-1),LAMBDA(a,b,LET(y,INDEX(x,b 1),VSTACK(a,y,HSTACK(TAKE(y,,1),MAP(DROP(y,,1),LAMBDA(c,SUM(--MID(c,SEQUENCE(LEN(c)),1))))))))))
  

  If you also need the totals then it's a matter of nesting some HSTACK() functions in there.