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.
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.)
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 & "*")
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)
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!
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 @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:54 AM