I have a column (let's say B1) in an Excel form that shows some products like this:
我在Excel表单中有一个列(比如说B1),它显示了一些像这样的产品:
Sand Systems
Gas Systems
Blenders
Other
Other 2
Other 3
Other 4
I need to count the rows that doesn't contain the text "Other #" (# being a number), in other words, "Sand Systems", "Gas Systems", "Blenders" and "Other" = 4.
我需要计算不包含文本“Other#”(#是一个数字)的行,换句话说,“Sand Systems”,“Gas Systems”,“Blenders”和“Other”= 4。
The solution so far was using an auxiliary column with the formula =SEARCH , but how to make it ignore the "Other" cell and accept "Other #"? Additionally, I wouldn't like to use an auxiliary column, but a cell with the =COUNTIF formula. Any advice?
到目前为止,解决方案是使用公式= SEARCH的辅助列,但如何使其忽略“其他”单元格并接受“其他#”?另外,我不想使用辅助列,而是使用= COUNTIF公式的单元格。任何建议?
2 个解决方案
#1
2
One approach is something like:
一种方法是这样的:
=COUNTIF(A1:A7,"<>" & "Other *")
This returns 4, which is the expected result?
这返回4,这是预期的结果?
Using the whole column reference will return the incorrect result, just FYI. If you're dead set on using whole column references use Ron Rosenfeld's formula:
使用整个列引用将返回不正确的结果,只是FYI。如果您已经开始使用整列引用,请使用Ron Rosenfeld的公式:
=COUNTA(A:A)-COUNTIF(A:A,"Other *")
#2
1
=COUNTA(A:A)-COUNTIF(A:A,"Other *")
Count them all, then subtract the one's that contain Other followed by space and anything else.
统计它们,然后减去包含其他的那个,然后是空格和其他任何东西。
#1
2
One approach is something like:
一种方法是这样的:
=COUNTIF(A1:A7,"<>" & "Other *")
This returns 4, which is the expected result?
这返回4,这是预期的结果?
Using the whole column reference will return the incorrect result, just FYI. If you're dead set on using whole column references use Ron Rosenfeld's formula:
使用整个列引用将返回不正确的结果,只是FYI。如果您已经开始使用整列引用,请使用Ron Rosenfeld的公式:
=COUNTA(A:A)-COUNTIF(A:A,"Other *")
#2
1
=COUNTA(A:A)-COUNTIF(A:A,"Other *")
Count them all, then subtract the one's that contain Other followed by space and anything else.
统计它们,然后减去包含其他的那个,然后是空格和其他任何东西。