Countif and dates???

[Scott]

I have a 20,000 entry spreadsheet that contains dates in this format
07/04/2010, As you can imagine there are quite a few of these entries
for each month. I want to count the number of them for a particular
month. I can't seem to use wildcards for some reason and cant find the
answer why on the net.

There formula that I would expect to work goes like this: - (ps I am
looking for all the March(03) entries in the column A)
=COUNTIF(A:A, "*/03/8")

This always returns a 0 and I can't see why?

In an ideal would I would like this to count the above AND also
countains the word EXAMPLE in the F:. I think the second part is much
easier than the first as I can easily use wildcards for searching for
things on their own.

Any help would be greatly appreciated as I am confused.com!

Scott

 

[Don Guillett]

maybe this. You may NOT use the entire column and shouldn't for other
formulas.
=sumproduct((year(a2:a22)=2003)*(month(a2:a22)=3)*(f2:f22="example"))

 

[Dave Peterson]

One way:

=sumproduct(--(text(a1:a10,"yyyymm")="201003"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another way is to count the number of dates before the next month and subtract
the number of dates before the month you want:

=countif(a:a,"<"&date(2010,4,1)) - countif(a:a,"<"&date(2010,3,1))

(=countif() can use entire columns in any version of excel)

 

[Scott]

Thanks Don,

point taken on using the entire column.

the first part works a treat but how about a wildcard for the example
part at the end? For instance human error means that people may
accidentally put just "exam" in or contain a rogue.(fullstop) or
something in the cell. So maybe I need to change this so that it is a
cell contain "exam" somewhere in the cell?

Many thanks!

Scott

 

[Don Guillett]

try ="exam*"

 

[Scott]

No that does not work?

 

[Don Guillett]

)*(left(f2:f22,4)="exam"))

 

[Dave Peterson]

oops. I missed the Example in column F stuff.

if the cell in column F contains Example (and nothing more):
=sumproduct(--(text(a1:a10,"yyyymm")="201003"),
--(f1:f10="example"))

if the cell in column F starts with Example and may have more stuff in the cell:
=sumproduct(--(text(a1:a10,"yyyymm")="201003"),
--(left(f1:f10,7)="example"))

if the cell in column F can have example anywhere:

=sumproduct(--(text(a1:a10,"yyyymm")="201003"),
--(isnumber(search("example",f1:f10))))

(Use =find() instead of =search() if you want it to be case sensitive)