Contextures

Contextures News 20180123

Excel COUNTIF Confusion

Jan 23, 2018

Can you solve this COUNTIF mystery, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

COUNTIF Confusion

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.

COUNTIF error

To get a correct count, I used an old, reliable function --SUMPRODUCT, instead of COUNTIF.

  • =SUMPRODUCT(--([Item]=[@Item]))

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.

Pivot Grouping

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.

add to data model

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.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Cityscapes

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!

weekly photo

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: http://www.contextures.com/newsletter/excelnews2018/20180123ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: January 29, 2018 2:59 PM