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