At the moment I’m working to make a sales report. In this report I have to sum up different values in several columns. To do this I use an arrayformula since the sheet is getting longer day by day.
The main problem is that in some of the columns there are N/A values. That makes it hard to do the calculation because if one of the calculated cells has an N/A value the total amount will not be calculated. Is there a trick to avoid this and don’t calculate N/A values and still use the arrayformula?
I tried =ARRAYFORMULA(if(A2:A<>"", IFNA(A2:A+B2:B+C2:C, ""), "")) but I keep getting the N/A errors. The basic present formula I use is: =arrayformula(if(A2:A<>"", A2:A+B2:B+C2:C, ""))
Link to example spreadsheet
Hope someone can help. Thanks!
[UPDATE] Adjusted link
>Solution :
try:
=ARRAYFORMULA(IF(A2:A<>"", A2:A+B2:B+N(C2:C), ))
your N/A is not a valid error but a text string. N() will convert anything to a number.
or try the new way:
=INDEX(IF(A2:A="",, BYROW(A2:C, LAMBDA(x, SUM(x)))))
