Last week, in my Contextures Newsletter, I posted an Excel COUNTIF challenge. There were 100 codes on a worksheet, but when counted, the occurrences for each letter only added up to 99. What was causing the problem? Take the challenge yourself – download the workbook to see how you’d solve it.
The Excel COUNTIF Challenge
Here the challenge that I posted in the newsletter —
Last week, someone emailed me their Excel file, because a simple COUNTIF formula wasn’t working. I made a sample file to show the problem, and there is a screen shot below.
- In column A, 100 cells have a one-letter code — A, B or C.
- To get a total count for each code, there are COUNTIF formulas in cells D2:D4.
In the screen shot below, you can see the list in column A, and the formula in cell D2.
That formula was copied down to cell D4.
Total Count is Wrong
Everything looks okay, but when those code counts are totaled, the result is 99, instead of 100, as you can see below.
- How would you troubleshoot that formula, to find the problem?
- And how would you fix it?
Download the Challenge Workbook
You can download the zipped sample file for the Excel COUNTIF Challenge. The zipped file is in xlsx format, and does not contain any macros.
- The problem is on the CountCodes sheet.
- Don’t look at the other sheets, until you’re ready to see the solution to this challenge.
Find the Problem
The wise and wonderful readers of my Contextures newsletter dove into the challenge with great enthusiasm. Some people immediately suspected what the problem was, and looked for proof of their assumption.
Other people were puzzled by the problem, and tried different troubleshooting techniques.
Many people sent me their solutions, and I compiled all of them into the Excel COUNTIF Challenge workbook.
Fix the Problem
After the problem was found, people chose different ways to fix it. There’s no one way that’s right or wrong – it depends on the situation, and whether you might run into the same problem again.
For example, this workbook has a one column list, and four cells with formulas. In a complex workbook, it might be harder to find the problem, and you might choose a different way to fix it.
Take the Excel COUNTIF Challenge
If you download the zipped sample file with the Excel COUNTIF Challenge workbook, let me know how you’d find and fix the problem.
And don’t read the comments below, if you don’t want to see a solution!