Count all fields in an column, except for n specif

问题:

I am trying to count all the field in an entire column except for n specified range. I need to select the entire column to cater for future expansion

currently i am trying:

=COUNTIF(B:B,"<>ASY999 OR <>ASY002 OR <>CIB001 OR <>""")

But the formula still counts the blank cell at the bottom of the column I get a return value of 65536 which is not correct

回答1:

Which version of Excel? In Excel 2007 or later try COUNTIFS (with an "S"), i.e.

=COUNTIFS(B:B,"<>ASY999",B:B, "<>ASY002",B:B,"<>CIB001",B:B,"<>")

I assume ASY999 etc are text values.....

In Excel 2003 or earlier you can use SUMPRODUCT but you need to restrict the range rather than using the whole column so assuming data in rows 2 to 1000 try

=SUMPRODUCT(ISNA(MATCH(B2:B1000,{"ASY999","ASY002","CIB001"},0))*(B2:B1000<>""))