Problems Counting Excel Data COUNTIF COUNTA

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.

Check for Duplicates

So, how did Twitter break my COUNTIF formula?

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.

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

The result should be 1, unless there are duplicates

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

countiferror03

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!

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

countiferror04

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.

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.

COUNTIF error

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.

=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:

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

Can you get that Microsoft formula to work?

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

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.

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.

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 ("").

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
  • 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
  • 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.

How would you fix the problem?

__________

Problems Counting Excel Data http://blog.contextures.com/

_____________

You may also like...

2 Responses

  1. Roger Govier says:

    Hi Deb
    Not a brilliant solution, but it does avoid the Error
    =COUNTIF([Item],LEFT([@Item],254)&”*”)

    It will give a false 2, instead of 1 with your example, but at least you wouldn’t get the #VALUE error message

Leave a Reply

Your email address will not be published. Required fields are marked *