Excel COUNTIF Confusion
Jan 23, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
Last week, one of my COUNTIF formulas returned a #VALUE! error, in a workbook that had been working nicely for years. The formula checks for duplicate content, when I'm collecting tweets for my weekly Excel Twitter post.
Recently, Twitter changed from a 140 character limit, to a 280 character limit, and that's what caused the error - COUNTIF/COUNTIFS can only check strings up to 255 characters. Here's a sample to show the problem. The 255 length works in row 4, but there is an error in row 5, with 256 characters.
To get a correct count, I used an old, reliable function --SUMPRODUCT, instead of COUNTIF.
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:
If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." For older versions of Excel, that error was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there's another thing that can prevent you from grouping -- adding the source data to the Data Model.
See how to fix problems when grouping pivot table items, and other issues you might run into with that "Data Model" type of pivot table.
Thanks to UniMord for reminding me about that issue. And if pivot tables are a big part of your job, my Pivot Power Premium add-in can help you save time.
Here are a couple of Excel articles that you might find useful or interesting.
Macros - Jon Acampora is hosting free webinars this week - "7 Steps to Getting Started with Macros & VBA". Jon explains why you'd want to learn VBA, and how to get started. There are 2 webinars each day, ending Friday, January 26th. Pick a date and time that works for you. If you want more than the basics, Jon also has a full macro course. (Level - Intermediate/Advanced)
Forecasting - On the Demand Planning site, Andrew Schneider suggests a couple Forecasting books, and gives a quick overview of Excel's forecasting tools. There's more info about the new Forecasting tool (Excel 2016) on the Microsoft site.. (Level - Intermediate/Advanced)
Our granddaughter came to visit on the weekend, and we did a "Cityscapes" art class. I used a ruler for the lines, and coloured in the buildings, as the instructor suggested. My granddaughter drew freehand lines, coloured one building, and then got bored. Her minimalist artwork looks great though -I need to get some of that free spirit!
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180123ctx.html
I'll also post any article updates or corrections there.
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:28 PM