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

user_11760970 0
Viewed 500

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

excel-2010 excel-formula excel
4 Answers

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.

user_91673820

In "Output" D2, formula copied down :

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

enter image description here

user_121251810

Another alternative: in the result column:

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

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)).

user_97581940
Related