Contextures News

Excel COUNTIF Challenge - Solved

November 8, 2016

Solution to the COUNTIF challenge, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Pivot Table Tip

If you're building a pivot table, you might need to add a calculated field. Unfortunately, there's no mouse shortcut to do that -- you have to go to the Analyze tab under Pivot Table Tools.

But, if the Home tab is showing on the Excel Ribbon, you can add it from there too. Just select a Value cell or heading in the pivot table, then click the Insert command. (If you select a Row or Column cell in the pivot table, you'll see Insert Calculated Item instead.)

Learn more about pivot table calculations on my website, and get my free pivot table add-in too!

insert calculated field

Fixing the Count

Thanks for taking part in last week's Excel challenge! The goal was to find out why a simple COUNTIF formula wasn't counting codes correctly, and then fix the problem. What a creative group you are -- thank you for sending your techniques for troubleshooting and solving the problem!

The count wasn't correct because one of the "A" cells had a space character at the end. It looks like all the other cells, but Excel doesn't see "A" and "A " as equal, so it didn't count that cell.

My solution was to use a wildcard character with the COUNTIF. I've updated the sample file, to show how I fixed it, and other awesome solutions that readers sent.

=COUNTIF(A$2:A$101,"*" & C2 & "*")

countif problem solved

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Shaded Rows - In the New York Times Personal Tech blog, see how to shade every other row in a worksheet, to make it easier to read. The first suggestion is conditional formatting, but I'd go with the Table style technique, if possible. (Level - All)

Printing Tips - The Journal of Accountancy has a few tips for printing all the sheets in a workbook. Just be careful when you group the sheets -- remember to ungroup them later! (Level - All)

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets.

A Stitch in Time

This lovely Cabbage Patch doll is getting a new dress for Christmas. She was my daughter's treasured friend for many years (a gift from my parents), and now my granddaughter plays with her. The sewing machine was my grandmother's, bought in the late 1940s, and still going strong. This project really is a stitch in time!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

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

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




Get weekly Excel tips from Debra






Last updated: March 15, 2017 11:54 AM