I often find myself helping people crunch numbers in Excel that are generated by some other database system and exported as CSV. These are generally log files of some sort. The data gets exported as CSV and pulled into Excel. Then you go about inserting formulas to get the various sums and counts that are necessary for whatever ad-hoc reporting objective is at hand.
CSV files are text files, but they their contents are often intended to represent numeric values. Simply setting the data format of a column isn’t enought to transform existing values from numbers-as-text to text-itself (forgive me if I’m starting to sound like Martin Heidegger, I don’t know a better way to put it). Changing the columns data type won’t help you for existing cells. It will only help with new cells or cells that you, after changing the format, happen to edit.
These numbers-as-text are problematic because certain key Excel count functions (COUNT(), COUNTIF()) silently ignore non-numeric values. COUNTA() can be used to count all non-blank cells. But, if you’re trying to apply COUNTIF() to these numbers-as-text, you’ll just keep getting zero–which is, actually, the correct answer.
What you need is the VALUE() function. It takes things that look like numbers (but, as far as Excel is concerned, are not numbers) and turns them into numbers. So, if you have a column of numbers that Excel insists on treating as text (say, column G, for example) you just create new column (let’s call it H), insert “=VALUE(G:G)”, and fill down. Then you can apply whatever COUNT() and COUNTIF() functions you like to column H.
There are three quick ways to tell if you’re looking at numbers-as-text instead of the real thing. By defult, Excel justifies numbers to the right and text to the left. So, if the data is freshly imported, that’s the most obvious attribute to check. But that setting can easily be overridden. So, if some other hands have been on the data, you can try counting the column (e.g. COUNT(G:G)), which should give you zero. Or, if you want to test each cell individually, you can create a new column, insert the T() function and fill down. T() returns nothing if the value is numeric. If it’s text, if will repeat the string it finds. So, if your original column and the one generated by T() are identical, you know you’re looking at text.