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(B26, "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(B26, "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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"smokief" wrote in message
...
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.