COUNTIF function help!!

[Celt]

I am having trouble with the COUNTIF Function. I am trying to use a wildcard in my criteria.

My data is all contained in column E. It looks like this

40400
40400
40420
40430
40500
40600

This is the formula I am using =COUNTIF(Sheet1!$E:$E,"404*")
Baisclly I want the function to return the number of rows that have either 40400, 40420 or 40430 (ie. 4).

If I change out the "404*" with 40400, it works perfectly.
Am I missing something?

[jellyarm]

I think countif will only work the way you want if you format the column you're checking as text, rather than general or number. I don't think wildcards work well with numeric data, you'd need to use operators for that (>,< etc)

hope this helps

Jellyarm

[Celt]

thanks for the advice Jelly.

Still having trouble figuring out how to define the number range in my criteria...

(ie. ">=40400" and "<=40430")

I must just be missing the boat. Excel doesn't seem to like anything I drop in there....

[Domenic]

Try the following...

=SUMPRODUCT(--(E1:E100>=40400),--(E1:E100<=40430))

Note that SUMPRODUCT doesn't accept whole column references.

Hope this helps!

thanks for the advice Jelly.

Still having trouble figuring out how to define the number range in my criteria...

(ie. ">=40400" and "<=40430")

I must just be missing the boat. Excel doesn't seem to like anything I drop in there....