Formula percent complete excluding given text string

[smokief]

I am doing a matrix and need help with a formula to exclude certain items
from a “percent completed†formula. I want to count the number of items in a
column of data except if the field contains the text “NAâ€. Then the formula
should return a percent complete on fields that contain any other character
or number from the total number possible (again, excluding the “NAâ€
characters).
Example:
=(Number of items with a character EXCEPT “NAâ€)/COUNTIF(L3:L58,"<>NA")
I got the first part of the formula from another posting, but can’t figure
out how to get the number of items that have a response excluding “NA†and
blanks. Any help you can provide would be much appreciated.

 

[Bernard Liengme]

The range A1:a6 contains:
#N/A, blank, z, #N/A, a, a
The formula =COUNTBLANK(A1:A6) returns 1 ( 1 blank)
Even if blank comes from formula such as =IF(B2>6, "a", "")
The formula =SUMPRODUCT(--(ISNA(A1:A6))) returns 2 (2 N/A)
The formula =COUNTA(A1:A6) returns 5 (5 that are not blank)
But if the blank in A2 came from formula such as =IF(B2>6, "a", ""), then
you get 6
The formula =SUMPRODUCT(--(NOT(ISNA(A1:A6)))) returns 4 (4 that are not NA)
Mix and stir to get desired result
best wishes

 

[smokief]

I used the COUNTBLANK and COUNTA to get my results. The "NA" was not a #N/A,
so the ISNA did not work correctly. Thanks very much for your quick response.
I'm able to carry on now.