Problems with COUNTIF function in Excel

Question

I'm having trouble with the countif function. The formula appears as:

=COUNTIF(L2:L55,">60")

L2:L55 is a list of numbers from about 30 to 80 and the result keeps returning as 0 which is clearly incorrect

please help.

Answer 1

I suggest that the numbers in L2:L55 are in text format. Try formatting as numeric as follows

• Select column L
• Set number format to General
• Type 1 (one) in any blank cell.
• Copy the cell with 1 in it.
• Select the range L2:L55, Right click over selection -> Paste Special -> Multiply -> OK

Answer 2

The column can be formatted as General or Numbers. However, if it is still not working then I am wondering if you have downloaded or imported the data and it has other non displayable characters prefixed or suffixed to the numbers.

Click on one of the cells in L2:L55 range and then click in the Formula bar and you should be able to determine with the right and left arrow keys if the cursor is moving across other non displayable characters.

You can use the Clean and Trim functions in another column to attempt to remove these superfluous characters and then copy the column with the formulas and Paste Special -> Values and then copy over the previous data.

Formula like the following to trim and clean the text. However, it is not really reliable and does not work with all non displayable characters.

=TRIM(CLEAN(L2))

You can also try using Text To Columns as follows and it might or might not work.

• Select the column
• Select Text to columns (on the Data ribbon)
• Select Fixed width and Next
• In the Data preview, remove any vertical lines either by selecting and dragging off the dialog or simply double click the vertical lines.
• Click finish

If you cannot get it to work then upload a workbook with a copy of the data to OneDrive and I will have a look at it. It might require VBA code to remove invalid characters.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

Answer 3

HI

Many thanks but unfortunately I did exactly as you said and it still returned 0.  Could you please tell me the reason for not formatting the cells as numbers.

Cheers

Answer 4

HI

Some how or other it seems to have worked without resorting to the above. The list is the age of patients calculated from the previous column which shows their dates of birth. one of the cells was blank so I don't know if that was the problem.

Thank you so much for your very prompt and helpful assistance.

Answer 5

HI

Some how or other it seems to have worked without resorting to the above. The list is the age of patients calculated from the previous column which shows their dates of birth. one of the cells was blank so I don't know if that was the problem.

Thank you so much for your very prompt and helpful assistance.

No, that was not the problem, I guess, the values more than 60 is in text mode ("60") that is why 60 (numerical) = "60" (Text) not matching and returns zero. That is why need to show your list. If it is shared through one drive then better. Otherwise paste a snapshot here.

Others related info:

• Make sure that, the list all are contains Numerical value (no Space, no text character)
• Another thing is, if you extract that list from any website (Internal ERP or others) and paste it in your column, then sometimes the developer used "" double quotation mark to return Numerical value, and if it has done, then you can't count properly the numerical value which is actually in TEXT value. You need to convert that list into numerical values
• You may also helped from my this article