excel countif_计算Excel数据COUNTIF COUNTA时出现问题

excel countif

Last week, I ran into problems counting Excel data with COUNTIF, and it's Twitter's fault! Why did they do that? The COUNTA function can cause problems too, when it counts cells that look empty. Let's see how to fix both of those issues.

上周，我遇到了使用COUNTIF计数Excel数据的问题，这是Twitter的错！ 他们为什么这样做？ 当COUNTA函数计数看起来为空的单元格时，也会引起问题。 让我们看看如何解决这两个问题。

检查重复 (Check for Duplicates)

So, how did Twitter break my COUNTIF formula?

那么，Twitter如何打破我的COUNTIF公式？

Every Thursday, I collect tweets for my weekly Excel Twitter post. The tweets are pasted into an Excel file, and a COUNTIF formula checks for duplicate content.

每个星期四，我都会为每周的Excel Twitter帖子收集推文。 这些推文将粘贴到Excel文件中，并且COUNTIF公式将检查重复的内容。

=COUNTIF(Used!C:C,H4)+COUNTIF(H:H,H4)

= COUNTIF(已使用！C：C，H4)+ COUNTIF(H：H，H4)

The result should be 1, unless there are duplicates

结果应为1，除非有重复项

Last Thursday, after years without problems, one row returned a #VALUE! error, instead of a number.

上周四，在没有问题的几年之后，一行返回了#VALUE！ 错误，而不是数字。

Twitter限制 (Twitter Limits)

Recently, Twitter changed from a 140 character limit, to a 280 character limit. My workbook has a formula that checks the length of each tweet, and that one was 267 characters – the longest tweet that I've ever pasted into the workbook!

最近，Twitter从140个字符的限制更改为280个字符的限制。 我的工作簿中有一个公式可以检查每条推文的长度，其中一个是267个字符，这是我粘贴到工作簿中的最长推文！

And yes, I have conditional formatting that highlights any tweet over 140 characters. Don't judge me.

是的，我有条件格式可以突出显示超过140个字符的任何推文。 不要判断我

COUNTIF个字符数限制 (COUNTIF Character Limit)

Unfortunately, Microsoft didn't raise its character limits, when Twitter increased theirs. That's what caused the error – COUNTIF/COUNTIFS can only check strings up to 255 characters. Other functions have the same limit.

不幸的是，当Twitter增加了字符限制时，Microsoft并未提高其字符限制。 这就是导致错误的原因– COUNTIF / COUNTIFS最多只能检查255个字符的字符串。 其他功能有相同的限制。

仅255个字符 (Only 255 Characters)

Here's another sample to show the problem. The 255 length works in row 4, but there is an error in row 5, which has one extra character – an X at the end.

这是另一个显示问题的示例。 255的长度在第4行有效，但在第5行有一个错误，该错误有一个额外的字符-结尾处是X。

To get a correct count, I used an old, reliable function --SUMPRODUCT, instead of COUNTIF. And since I was improving the formula, I created named ranges too, and wrapped it with an IF function.

为了获得正确的计数，我使用了一个可靠的旧函数--SUMPRODUCT，而不是COUNTIF。 由于我正在改进公式，因此我也创建了命名范围，并用IF函数将其包装起来。

=IF(H4="","",SUMPRODUCT(--(Tweets_Used=H4)) +SUMPRODUCT(--(Tweets_New=H4)))

= IF(H4 =“”，“”，SUMPRODUCT(-(Tweets_Used = H4))+ SUMPRODUCT(-(Tweets_New = H4)))

令人困惑的解决方法 (Confusing Workaround)

Later, I checked Microsoft's COUNTIF page, and it says you can get around the 255 limit, by joining two long strings with the concatenate operator (&). That suggestion did NOT work for me though - maybe I'm missing something:

后来，我检查了Microsoft的COUNTIF页面 ，它说，您可以通过使用连接运算符(＆)连接两个长字符串来绕过255个限制。 但是，该建议对我不起作用-也许我缺少了一些东西：

• =COUNTIF(A2:A5,"long string"&"another long string")

  = COUNTIF(A2：A5，“长字符串”＆“另一个长字符串”)

Can you get that Microsoft formula to work?

您可以使该Microsoft公式起作用吗？

Click here to download my sample file, and see more COUNTIF examples on my website.

单击此处下载我的示例文件 ，并在我的网站上查看更多COUNTIF示例 。

COUNTA计算空单元格 (COUNTA Counts Empty Cells)

In other Excel count functions news, I've done a major update on my blog post about problems counting Excel data when cells look empty, but they aren't.

在其他Excel计数函数新闻中，我对博客文章进行了重大更新，内容涉及当单元格看起来为空时对Excel数据进行计数的问题 ，但事实并非如此。

In the screen shot below, data was copied from an Access database, and pasted into Excel. The COUNTA formula in cell C2 is counting those "blank" cells, even though they look empty.

在下面的屏幕快照中，从Access数据库复制了数据，并将其粘贴到Excel中。 单元格C2中的COUNTA公式正在计算那些“空白”单元格，即使它们看起来是空的。

其他原因 (Other Causes)

It's not just data from Access that creates these strange "blank" cells. They're also created if you convert formulas to values, and some of the formulas returned an empty string ("").

产生这些奇怪的“空白”单元的不只是来自Access的数据。 如果将公式转换为值，也会创建它们，并且某些公式返回空字符串(“”)。

This screen shot shows that type of formula, and when pasted as values, the empty string cells are counted.

此屏幕快照显示了该公式的类型，并在粘贴为值时对空字符串单元格进行计数。

请参阅隐藏的内容 (See Hidden Contents)

In the update, I added a tip that lets you see something in those "blank" cells.

在更新中，我添加了一个提示，使您可以在那些“空白”单元格中看到某些内容。

• On the Excel Ribbon, click the File tab
  在Excel功能区上，单击“文件”选项卡
• At the left, click Option
  在左侧，单击选项
• In the Category list, click Advanced
  在类别列表中，单击高级。
• Scroll down to the end of the Advanced options, and look for the Lotus Compatibility section
  向下滚动到“高级”选项的末尾，然后查找“ Lotus兼容性”部分。
• Add a check mark to Transition Navigation Keys
  在过渡导航键上添加复选标记

After you turn this option on, click on a cell that looks blank, and check the Formula Bar. You should see an apostrophe there.

打开此选项后，单击看起来为空白的单元格，然后检查编辑栏。 您应该在那里看到一个撇号。

Remember to turn this option off later, when you've finished the troubleshooting.

完成故障排除后 ，请记住稍后关闭此选项 。

解决问题 (Fix the Problem)

My original solution was to use Find and Replace. The blanks were replaces with $$$$, and then the $$$$ were replaced with nothing. There are instructions to manually do those steps, and there's a macro too.

我最初的解决方案是使用“查找并替换”。 将空白替换为$$，然后将$$$替换为空白。 有说明手动执行这些步骤，还有一个宏。

The update has 2 new solutions, thanks to the people who posted their suggestions in the comments section.

此更新有2个新解决方案 ，这要归功于在评论部分发表了建议的人们。

How would you fix the problem?

您将如何解决该问题？

翻译自: https://contexturesblog/archives/2018/01/25/problems-counting-excel-data-countif-counta/

excel countif