'How to Sum NA (text NA) values in excel with numbers by treating NA as 1

I have a table

    A           B      C
 1  Param1  Param2   Param3
 2   1        NA      NA
 3   0         1      NA
 4   0         0      NA
 5   1         NA      1

Expected output

      A           B      C     D
 1  Param1  Param2   Param3  Output
 2   1        NA      NA       3
 3   0         1      NA       2
 4   0         0      NA       1
 5   1         NA      1       3   #(sum of A,B,C columns is Output basically)

So basically I want excel to treat NA as 1 . NA Is text here (its not error, its plain text NA).

I tried below but doesn't work

=sum(A2, B2,C2)

This code ignores NA. I thought NA is string so will be summed too. Can you guys help

Solution 1:^[1]

In "Output" D2, formula copied down :

=SUMPRODUCT(0+TEXT(A2:C2,"0;;0;\1"))

Solution 2:^[2]

Another alternative: in the result column:

=SUM(IF(A3:C3="NA",1,A3:C3))

Solution 3:^[3]

The easiest that comes to mind is to use COUNTIF(), probably like:

=3-COUNTIF(A2:D2,0)

Or:

=COUNTIF(A2:C2,"<>0")

Another little trick is to check if a cell is larger than 0:

=SUMPRODUCT(--(A2:C2>0))

Since both 1 and N/A are larger than 0 this will evaluate to the proper amount.

If one is playing code golf and you have O365, you can do: =SUM(--(A2:C2>0)).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	bosco_yip
Solution 2	DS_London
Solution 3

'How to Sum NA (text NA) values in excel with numbers by treating NA as 1

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]