计算列中的所有字段,除了 n 个指定范围 [英] Count all fields in an column, except for n specified range
问题描述
我正在尝试计算整列中除 n 指定范围外的所有字段.我需要选择整个列以适应未来的扩展
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
目前我正在尝试:
=COUNTIF(B:B,"<>ASY999 OR <>ASY002 OR <>CIB001 OR <>""")
但公式仍然计算列底部的空白单元格我得到的返回值是 65536,这是不正确的
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
推荐答案
哪个版本的 Excel?在 Excel 2007 或更高版本中尝试 COUNTIFS(带有S"),即
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,"<>")
我假设 ASY999 等是文本值.....
I assume ASY999 etc are text values.....
在 Excel 2003 或更早版本中,您可以使用 SUMPRODUCT,但您需要限制范围而不是使用整列,因此假设第 2 行到 1000 行中的数据尝试
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<>""))
这篇关于计算列中的所有字段,除了 n 个指定范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!