CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
noob
noob

Reputation: 3811

Summing Text strings and numbers togather in excel

I have a table

    A           B      C             D              E
 1  Param1  Param2   Param3  CompulsoryParam4   CompulsoryParam5
 2   1        NA      NA             1              0
 3   0         1      NA             1              1
 4   0         0      NA            0               0
 5   1         NA      1             1               1

Expected output

        A           B      C             D              E             F
 1  Param1  Param2   Param3  CompulsoryParam4   CompulsoryParam5   Output
 2   1        NA      NA             1              0                 
 3   0         1      NA             1              1                4
 4   0         0      NA             0              0                
 5   1         NA      1             1              1                5  (sum of all A,B,C,D,E columns treating NA as 1, sum only if both D and E are 1)

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

So i want to sum only if both D and E columns have 1 each else not sum. Code tried .Each column has only values 1,0 or NA

     = IF(PRODUCT(D2:E2=1),(5-COUNTIF(A2:D2,0)),"")

This doesnt seem to work. Can you please tell what im doing wrong

Upvotes: 1

Views: 37

Answers (1)

Michal
Michal

Reputation: 5740

You have a typo in your formula - IF(PRODUCT(D2:E2)=1,(5-COUNTIF(A2:D2,0)),"X")

Upvotes: 1

Related Questions