# Take the Excel COUNTIF Challenge

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.

=COUNTIF(A:A,C2)

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?

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!

_________________

Save

### 19 Responses

1. Gary Keramidas says:

I just did a simple len() formula to see if there were any spaces

2. Rod says:

Noticed your site redesign happens in 2106. Whee! 90 more years of learning Excel

3. Ian G says:

I like the wildcard concatenated in the countif solution, though personally I’d create a trim column like FixB. I’m not a fan of changing the data like fix A as you’ll have to repeat it every time you get an updated data set

4. Julian says:

I reasoned that there may be spaces so I did a [Data – Filter] to list the (what I thought would be) unique values. This didn’t show A[SPACE] as a separate value. This surprises me!
Great exercise

• @Julian, you’re right, and I’m not sure why Excel’s AutoFilter ignores trailing spaces

• bromkovsky says:

Actually have always noticed that in filter it does a quick clean of the data. Great when you’re trying to select all “A” values regardless of leading and trailing spaces, but stinks when trying to figure out why exact matches didn’t work.

Pivot table the values works as well and doesn’t roll up the “A ” with the “A”

• Terry says:

This one tricked me. I did a filter and found 33 As, but didn’t know why the count was only 32. I didn’t realise the filter did this ‘cleanup’. Dangerous, and good to know.

• @Terry, thanks, and a pivot table keeps the original entries separated, but an AutoFilter does that “helpful” cleanup.

5. Firas says:

Row No. 5 has “A ” with space.
I applied the countif() function for every row. then check 0 and 1 for each row.
to fix this I suggest using Trim()

6. Mark says:

I suspected a space. I did Data > Text to Columns on the data, with space as delimiter, and the count corrected itself. (Didn’t know which cell it was in until I checked the solutions sheet.)

• Thanks Mark! You’re the first to mention Text To Columns — nice fix!

7. Vasant K says:

Debra Dalgleish , Cell A6 had an additional space which was why…

8. Manish says:

I used to check teh formula to figure out where value is not changing
=IF(A2=”A”,B1+1,B1) and found A5 had an issue.. Used trim to sort out it

9. Ian says:

I’m sure this misses the point, but why would you use countif() on an array with multiple conditions when you haven’t cleansed the data? Easiest way to get the right answer is put this in D2, {=SUM(IF(TRIM(A:A)=C2,1,0))}, this in D3, {=SUM(IF(TRIM(A:A)=C3,1,0))} and this in D4, {=SUM(IF(TRIM(A:A)=C4,1,0))}. Could add Upper() test as well etc etc. Or even a larger array formula across the solution range