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.
Best Answer
I ended up using the following formula, under assumption that trying to
SUMa non-number returns an error, which seems to be correct when given???: