Hi everyone,

I have column filled with dates that, unfortunately, are text and therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month). My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce much
faster a result than counting and checking individual cells with VBA.

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

How about:

=COUNTIF(C1:C6,"*-10-*")

Regards,
Fred.

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Hi

Try
=COUNTIF(A:A,"=*10*")

--
Regards
Roger Govier

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Hi Bob,

Yep, this is what I wanted. Works great.

Thank you,
Cooz


"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Hi Fred,

Ok! You can use wildcards with countif! I learn a lot here.
Thank you,

Cooz

"Fred Smith" wrote:

How about:

=COUNTIF(C1:C6,"*-10-*")

Regards,
Fred.

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Good idea. Works fine.
Thank you,

Cooz


"Roger Govier" wrote:

Hi

Try
=COUNTIF(A:A,"=*10*")

--
Regards
Roger Govier

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Hello Bob,

If I may take some more of your time...

When I look in the XL help on SUMPRODUCT, I can't find anything on using
this function with criteria. Yet your solution works perfectly. The help
doesn't mention "--" either.

Adding both: Does the "--" operator denote the usage of criteria? Can I use
this operator with other functions as well?

Thank you once again,

Cooz


"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cooz" wrote in message
...
Hello Bob,

If I may take some more of your time...

When I look in the XL help on SUMPRODUCT, I can't find anything on using
this function with criteria. Yet your solution works perfectly. The help
doesn't mention "--" either.

Adding both: Does the "--" operator denote the usage of criteria? Can I
use
this operator with other functions as well?

Thank you once again,

Cooz


"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count
all
dates in the column that represent a day in, say, October (or any
month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be
substituted
for XXX. Can this be done with a worksheet function and if so, what
would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

I would suggest using the dashes around the 10 like Fred showed, otherwise
the formula fails for the 10 of any month.

Rick


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Try
=COUNTIF(A:A,"=*10*")

--
Regards
Roger Govier

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any month).
My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be substituted
for XXX. Can this be done with a worksheet function and if so, what would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.

Very true, Rick. Mea Culpa.

--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in
message ...
I would suggest using the dashes around the 10 like Fred showed, otherwise
the formula fails for the 10 of any month.

Rick


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Try
=COUNTIF(A:A,"=*10*")

--
Regards
Roger Govier

"Cooz" wrote in message
...
Hi everyone,

I have column filled with dates that, unfortunately, are text and
therefore
cannot be viewed as numbers. The problem I have to solve is to count all
dates in the column that represent a day in, say, October (or any
month). My
column looks like this:

A
12-10-2009
14-11-2009
17-10-2009
17-10-2009
15-10-2009
12-11-2009
(etcetera)

It would be nice if I could use something like
=COUNTIF(A1:A6;MID(XXX;3;4)="-10-")
but of course this won't work; there's nothing concrete to be
substituted
for XXX. Can this be done with a worksheet function and if so, what
would
this function look like?

Thank you,
Cooz

PS I use Excel 2000
PS I know how to do this with VBA but since the A1:A6 range I mentioned
above is actually Much Longer, using a worksheet function would produce
much
faster a result than counting and checking individual cells with VBA.