# Countif Formula not working, why?

This is a discussion on Countif Formula not working, why? within the Excel Questions forums, part of the Question Forums category; I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more ...

1. ## Countif Formula not working, why?

I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me. HELP!!!

2. ## Re: Countif Formula not working, why?

Hi and welcome to the Board!!
Use SUMPRODUCT
Code:
`=SUMPRODUCT(--(F4:F500>=4000)*--(F4:F500<5000))`
lenze

3. ## Re: Countif Formula not working, why?

The result that I got from your formula is the same as mine. It shows a zero (0) in the cell I am trying to put the formula in. Anything else you can think of?

4. ## Re: Countif Formula not working, why?

It sounds like the numbers in F4:F500 are not really numbers, but "Numbers stored as Text"
This is a very common and annoying problem...

Try
=SUMPRODUCT(--(LEFT(F4:F500,1)="4"),--(LEN(F4:F500)=4))

Or even better, use Data - Text to Columns to convert the "numbers stored as text" to actual numbers

5. ## Re: Countif Formula not working, why?

tried the new formula it didn't work and I tried the text to columns and that didn't help. Anything else maybe?

6. ## Re: Countif Formula not working, why?

Check you cells to see if they are numbers
=ISNUMBER(F4) should return TRUE
If not, Text to Columns should fix that or
Jonmo's formula should work

lenze

7. ## Re: Countif Formula not working, why?

I created a new column next to the F column. I entered =isnumber(F4) at the top and I dragged it down to the very last number I had in the F column and next to all my numbers it says true. Any ideas where I should look?

8. ## Re: Countif Formula not working, why?

I would now be checking if the values of the numbers are what you think they are.

Maybe there is some special format on the cells that makes it look like 4000, when it really isn't...

Try copying the f column, paste specail values onto another column.

format that column as General

does that reveal anything?

9. ## Re: Countif Formula not working, why?

I copied the column and pasted it into a brand new worksheet. I then made the column wider. All my numbers were on the right side of the column. I made did a format on them to general and nothing changed. I did a format to text and they all went to the left side of the column. I took the two formulas I got from Mr Excel and put them on the same worksheet and changed the columns from "F" to "D", the column I am using on the new worksheet, and the formulas gave the same results. I tried my countif formula and I got the same results as before. Then I tried just a specific number and the formula worked just like in my original post. Anything else maybe I could try?

10. ## Re: Countif Formula not working, why?

What is the LEN of your cells
=LEN(F4)

lenze

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•