What I want to do is add up all the individual digits in a number from the adjacent cell. For instance, if the value is 24, I want the result to be 6 (because 2+4=6). The formula works fine in a single cell but won’t extend to other rows automatically. How can I make this work properly with ArrayFormula so it processes the entire column range instead of just one cell at a time?
The issue lies in how INDIRECT and ADDRESS interact inside ArrayFormula. Instead, you can streamline your formula using SUMPRODUCT and MID. Consider trying this: =ArrayFormula(IF(A:A<>"",SUMPRODUCT(--MID(A:A,ROW(INDIRECT("1:"&LEN(A:A))),1)),"")). However, keep in mind that working with digits across columns can still be cumbersome. Sometimes, a helper column using a simpler formula like =SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)) might be more effective.
the issue is ur using ROW() and COLUMN() inside arrayformula which doesnt expand properly. try this instead: =ArrayFormula(IF(A2:A<>"",SUMPRODUCT(--MID(A2:A,ROW(INDIRECT("1:"&MAX(LEN(A2:A)))),1)),"")) replace A2:A with ur actual range. this should work better for the whole col.
I ran into this exact problem last month and found that ArrayFormula gets confused when you reference relative positions with ROW() and COLUMN() functions. The workaround that finally worked for me was breaking it down differently. Try using =ArrayFormula(IF(A2:A="","",MMULT(--(MID(A2:A,COLUMN(INDIRECT("1:"&MAX(LEN(A2:A)))),1)>=0)*MID(A2:A,COLUMN(INDIRECT("1:"&MAX(LEN(A2:A)))),1),TRANSPOSE(COLUMN(INDIRECT("1:"&MAX(LEN(A2:A))))^0))) instead. It uses matrix multiplication to sum the digits which handles the array expansion much better than SUMPRODUCT in this context. Make sure to adjust A2:A to match your actual data range. The formula looks complex but it processes each number character by character across the entire range without the referencing issues you’re experiencing.