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

Question

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

Answer 1

By default COUNTIF counts specific cells and returns a number. This gives you the ability to type in D1 (or D2):

=COUNTIF(A3:C3,"NA")+SUM(A3:C3)

Which counts the number of cells that contain "NA" and then adds the sum of the numbers.

You can then drag the formula down to apply the same logic to all rows.

Answer 2

In "Output" D2, formula copied down :

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