COUNTIF not working

Copper Contributor


I am working on a project where I am analyzing a data set on excel. I am trying to count the total number of skiers from each nationcode by using the COUNTIF function, but it is not working. I am following all the instructions online on how to add in the range of cells as well as the criteria. When I press enter for excel to do the calculations the text with the "=COUNTIF(K2:K12393;L2:L97)" remains in the cell as if it is a text. 

Does anyone know how I solve this issue?


9 Replies


Make sure that the cell in which you enter the formula is NOT formatted as Text, but as General or as Number.

Make sure that the Show Formulas button in the Formula Auditing group of the Formulas tab of the ribbon is NOT highlighted.

Make sure that your formula is =COUNTIF(...) and not "=COUNTIF(...)".

did you manage to solve the problem? i have the same problem and have spent around two hours without success @emmaswa 


We never heard back from @emmaswa 

Did you look at my suggestions? If they don't help:

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?


column is is concatation of a thru all values in column J should be 1.


Please help me, i think i posted my question incorrectly


Internally COUNTIF converts text which looks like numbers into the numbers keeping first 15 digits


The trick could be


or you may use SUMPRODUCT() instead.




I understand, both methods worked for me and my issue is resolved.

Thanks a million+1


@LxGwy2024 , you are welcome