Formula for If - SUMIF

[Guest]

Excel 2003, what is the formula?

I have 2 columns of data. If there are X's in cells in column 1, I need to
count the number of coresponding cells in column 2 that have a specific
criteria (N/A).

Colimn 1 has an X or is blank, column 2 has 1, 2, 3, 4, 5 or N/A.

 

[Guest]

Keith, what if you did a "vlookup" first and then sunif. this might be a
longer way round but it may work.

 

[Biff]

Is it TEXT N/A or the error value #N/A ?

For TEXT N/A:

=SUMPRODUCT(--(A1:A10="X"),--(B1:B10="N/A"))

For error value #N/A:

=SUMPRODUCT(--(A1:A10="X"),--(ISNA(B1:B10)))

Biff