I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates are
listed in the same cell? Many thanks

Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

"Tendresse" wrote in message
...
I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates
are
listed in the same cell? Many thanks

Ooops!

Hold on there just a second.

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4
Copy down 12 rows for the 12 months

That'll get tripped up on double digit month numbers.

Try this version instead:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4

Biff

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

"Tendresse" wrote in message
...
I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates
are
listed in the same cell? Many thanks

Hi T. Valko, thanks for your reply, but unfortunately it doesn't seem to be
working. The result I got was the following:

01/01/1900

Any ideas?

Ta,

"T. Valko" wrote:

Ooops!

Hold on there just a second.

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4
Copy down 12 rows for the 12 months

That'll get tripped up on double digit month numbers.

Try this version instead:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4

Biff

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

"Tendresse" wrote in message
...
I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates
are
listed in the same cell? Many thanks

Try formatting that cell as General (not a date).


Tendresse wrote:

Hi T. Valko, thanks for your reply, but unfortunately it doesn't seem to be
working. The result I got was the following:

01/01/1900

Any ideas?

Ta,

"T. Valko" wrote:

Ooops!

Hold on there just a second.

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4
Copy down 12 rows for the 12 months

That'll get tripped up on double digit month numbers.

Try this version instead:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4

Biff

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

"Tendresse" wrote in message
...
I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates
are
listed in the same cell? Many thanks






--

Dave Peterson

Thanks, Dave. We are getting very close. Well, it seems to be counting all
dates in "January" very well.
Now, what do i need to replace in the formula so that it counts other months
as well. Where does it refer to different months?

Many thanks

"Dave Peterson" wrote:

Try formatting that cell as General (not a date).


Tendresse wrote:

Hi T. Valko, thanks for your reply, but unfortunately it doesn't seem to be
working. The result I got was the following:

01/01/1900

Any ideas?

Ta,

"T. Valko" wrote:

Ooops!

Hold on there just a second.

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4
Copy down 12 rows for the 12 months

That'll get tripped up on double digit month numbers.

Try this version instead:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4

Biff

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

"Tendresse" wrote in message
...
I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates
are
listed in the same cell? Many thanks






--

Dave Peterson