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

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.

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.

"Bernard Liengme" wrote:

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.