CountIF issue

Hi guys,

I am using the COUNTIF function to count the number of students who got each type of grade A* to U in a test. However it treats A* and A as the same. I have different referance cells, have tryed formatting as text but don't seem able to get it to see the differance. The cells being counted contain formulas, but it dosen't seem to mind for all the other grades which it counts correctly?

Any help or advice welcome.

Thank you.

Excel uses * as a "wildcard" if you want * to be recognised as a literal "*" and not as a wildcard then place a tilde [~] in front, e.g.To count A*s in B1:B10

=COUNTIF(B1:B10,"A~*")

Brill, works fine, thank you,

Not sure what wildcards do?, I would never have got around it without your help. Need do some homework on these I think,

Have a Happy New year Mr Daddylonglegs!

I am trying to count the number of occurrences with a date list by year. I have list of dates that are spread out over several years with some Text note in the adjacent column proclaiming something happen on that date.

The data entered into the cell is 12/31/2006 format.

I have tried the count function which returns a total count of entries regardless of the year. I then tried the countif function which return a value of zero.

I tried wild cards (* and ~) to no avail thought this is the first time using them. Is it the wild cards or the count function that I am messing up?

Please Login or Register  to view this content.

Originally Posted by MDubbelboer

your issue here lies in the fact that even though the date displays as mm/dd/yyyy it is infact an integer.
for example 1/1/2006 is equal to integer 38718.
If you take your date cell and convert the format to text you'll see what i mean.
if you want to countif for 2006 you'd have to use two criteria, greater or equal than 1/1/2006 and less than 1/1/2007.
easiest way to do this is sumproduct

=sumproduct(--(A1:A100>=38718),--(a1:a100<39082))
where a1:a100 is your date range

Thanks Again

hth

Thanks that works.

A couple of Questions:
1. Is the date integer a format of juilian ??
2. How do I convert the a date to an integer?
3. What are the dashes [--] doing in the function? Just curious on this one.

You have to be careful with a formula like

=sumproduct(--(A1:A100>=38718),--(a1:a100<39082))

If you use 1904 date system this won't work. More robust (it works whichever date system you use) and more transparent

=SUMPRODUCT(--(YEAR(A5:A93)=2006))

Originally Posted by daddylonglegs

You have to be careful with a formula like

=sumproduct(--(A1:A100>=38718),--(a1:a100<39082))

If you use 1904 date system this won't work. More robust (it works whichever date system you use) and more transparent

=SUMPRODUCT(--(YEAR(A5:A93)=2006))

Thanks to both of you. I learn something new today so I get to go home.