Using ARRAYFORMULA extensively in complex formulas

Question

I have a formula which looks like this:

=ARRAYFORMULA(IF(A4:A = "", , IF(AND(ISNUMBER(I4), ISNUMBER(J4), ISNUMBER(K4), ISNUMBER(L4)), SUM(I4:L4), "???")))

The idea is that for each row in the sheet, those four cells are added if and only if they are all valid number, otherwise ??? is displayed.

What happens in the above formula is that each row is set to the sum for the 4th row, and only if values in the 4th row are numbers.

If I put a range in ISNUMBER, then it checks if ALL of the column is a valid number, and I don't really know how to define a SUM for this case.

How do I make it act as I described? I need the ARRAYFORMULA since this is a part of the sheet that takes the responses from another sheet and does calculations on the input. Since each response is added as a new row, all my formulas get shifted (and thus, they omit the new response), unless I use ARRAYFORMULA.

Answer 1

I ended up using the following formula, under assumption that trying to SUM a non-number returns an error, which seems to be correct when given ???:

=ARRAYFORMULA(IF(A4:A = "", , IFERROR(I4:I+J4:J+K4:K+L4:L, "???")))

Answer 2

@Karlovsky120, if you're needing this to work in an array from row 4 down, try this:

=ArrayFormula(IF(A4:A="",, MMULT(IF(ISNUMBER(I4:L), I4:L, 0), SEQUENCE(4, 1, 1, 0))))

HOW IT WORKS:

The key bit is the MMULT, which multiplies a matrix by another matrix. Each matrix must consist of numbers (i.e., no text, no nulls). So the IF clause just acts to substitute anything that isn't a number with a zero. Once that's done, I4:L will form Matrix #1 consisting of n rows and 4 columns. The second argument of MMULT (i.e., Matrix #2) must have the same number of rows as the number of columns in Matrix #1: in this case, that's 4. And since we just want to return the values themselves, we only need to multiply every value in Matrix #1 by 1. So Matrix #2 would just be four 1's, stacked vertically.

That is formed by the SEQUENCE function, which reads "Form a sequence of numbers that has 4 rows, 1 column, starts at 1 and moves by 0 each step."

Once we have the two matrices, we multiply them (which, oddly enough, means something closer to "add the products of each matching combination").

The attached image show MMULT working with just three elements instead of four, but it may be easier to understand.

Above, the left-side matrix represents your actual row data, and the middle (yellow) matrix would be the stacked 1's generated by SEQUENCE. In the end, what you get is the right-side result: an array that sums ranges row by row.