Adding months
 

In sheet 2 I'm creating at a glance information from the main sheet 1. In
column F is my dates, Example is 6/15/2006. I would like to create a formula
that will tell me how many cells have January 2006, February 2006, etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

Hi Peter

One way
On sheet2 in F1 type 1996-01, in F2 1996-02 etc. for all of the
Year-month ranges you are interested in.
In F2
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=F1))
and copy down

Alternatively, if you want to put Excel dates in column F of sheet2,
then
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=TEX(F1,"yyyy-mm")))

--
Regards

Roger Govier


"Peter" wrote in message
...
In sheet 2 I'm creating at a glance information from the main sheet 1.
In
column F is my dates, Example is 6/15/2006. I would like to create a
formula
that will tell me how many cells have January 2006, February 2006,
etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

array formula on sheet 2 : cell B2

=(SUM((MONTH('main sheet 1'!F1:F1000)=MONTH(A1))*(YEAR('main sheet
1'!F1:F1000)=YEAR(A1))))&" count of dates that fall within month of
"&TEXT(A1,"MMMM-YYYY")
hit F2 then press CTRL-SHFT-ENTER....

place your date on A1 (e.g. type 1/1/06 : January 2006)
the formula count the dates that fall with the month of january in year
2006.only

happy holidays....

"Peter" wrote:

In sheet 2 I'm creating at a glance information from the main sheet 1. In
column F is my dates, Example is 6/15/2006. I would like to create a formula
that will tell me how many cells have January 2006, February 2006, etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

Im so new to this I find it difficult to understand some of the functions. I
did however make a few mistakes. Sheet1 has all of vital information. Column
C starting with Row 3 has all of the dates. The date format starts with
5/17/1995 and ends in Row 329 with the date 12/1/2006. The information I
would like to extract to Sheet2 will tell me how many cases I signed in any
given month. As an example the month of April may show I signed 25 cases and
the month of May may show 15 cases. The information will be from the entire
period stating in 1995 and ending in 2006 or when the dates end in the
spread sheet. Sorry I wasnt more specific before. I do appreciate all the
help.

Thank again,

Peter


"Roger Govier" wrote:

Hi Peter

One way
On sheet2 in F1 type 1996-01, in F2 1996-02 etc. for all of the
Year-month ranges you are interested in.
In F2
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=F1))
and copy down

Alternatively, if you want to put Excel dates in column F of sheet2,
then
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=TEX(F1,"yyyy-mm")))

--
Regards

Roger Govier


"Peter" wrote in message
...
In sheet 2 I'm creating at a glance information from the main sheet 1.
In
column F is my dates, Example is 6/15/2006. I would like to create a
formula
that will tell me how many cells have January 2006, February 2006,
etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

Hi Peter

If I am understanding you correctly, then you want all results totalled
for the month of April, regardless of which year it was.
If that is the case, and with your new information as to where the dates
are located, then
If F1 enter 01/01/06, in F2 enter 02/01/06 and onward for each of the 12
months (the Year is unimportant here, so 2006 is OK)
On Sheet2 in G1 enter
=SUMPRODUCT(--(Month(Sheet1!$C$3:$A$1000)=Month(F1)))
Copy the formula down through G2:G12

--
Regards

Roger Govier


"Peter" wrote in message
...
I'm so new to this I find it difficult to understand some of the
functions. I
did however make a few mistakes. Sheet1 has all of vital information.
Column
C starting with Row 3 has all of the dates. The date format starts
with
5/17/1995 and ends in Row 329 with the date 12/1/2006. The information
I
would like to extract to Sheet2 will tell me how many cases I signed
in any
given month. As an example the month of April may show I signed 25
cases and
the month of May may show 15 cases. The information will be from the
entire
period stating in 1995 and ending in 2006 or when the dates end in
the
spread sheet. Sorry I wasn't more specific before. I do appreciate all
the
help.

Thank again,

Peter


"Roger Govier" wrote:

Hi Peter

One way
On sheet2 in F1 type 1996-01, in F2 1996-02 etc. for all of the
Year-month ranges you are interested in.
In F2
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=F1))
and copy down

Alternatively, if you want to put Excel dates in column F of sheet2,
then
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=TEX(F1,"yyyy-mm")))

--
Regards

Roger Govier


"Peter" wrote in message
...
In sheet 2 I'm creating at a glance information from the main sheet
1.
In
column F is my dates, Example is 6/15/2006. I would like to create
a
formula
that will tell me how many cells have January 2006, February 2006,
etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

Roger

I'm not sure I understand "If F1 enter 01/01/06, in F2 enter 2/1/06 and
onward" as all the dates on Sheet1 are located in Column C starting at Row 3
and ending at C329. For the year 1998 this is how the dates read;
1/21/1998
2/25/1998
3/9/1998
3/18/1998
3/25/1998
3/25/1998
4/1/1998
4/14/1998
4/27/1998
4/28/1998
5/8/1998
5/18/1998
5/29/1998
6/10/1998
6/18/1998
7/4/1998
7/22/1998
8/22/1998
8/26/1998
9/4/1998
9/5/1998
9/9/1998
9/12/1998
9/27/1998
9/30/1998
10/13/1998
10/27/1998
10/28/1998
11/6/1998
11/6/1998
11/23/1998
Is this helpful to you?

Thanks

Peter

"Roger Govier" wrote:

Hi Peter

If I am understanding you correctly, then you want all results totalled
for the month of April, regardless of which year it was.
If that is the case, and with your new information as to where the dates
are located, then
If F1 enter 01/01/06, in F2 enter 02/01/06 and onward for each of the 12
months (the Year is unimportant here, so 2006 is OK)
On Sheet2 in G1 enter
=SUMPRODUCT(--(Month(Sheet1!$C$3:$A$1000)=Month(F1)))
Copy the formula down through G2:G12

--
Regards

Roger Govier


"Peter" wrote in message
...
I'm so new to this I find it difficult to understand some of the
functions. I
did however make a few mistakes. Sheet1 has all of vital information.
Column
C starting with Row 3 has all of the dates. The date format starts
with
5/17/1995 and ends in Row 329 with the date 12/1/2006. The information
I
would like to extract to Sheet2 will tell me how many cases I signed
in any
given month. As an example the month of April may show I signed 25
cases and
the month of May may show 15 cases. The information will be from the
entire
period stating in 1995 and ending in 2006 or when the dates end in
the
spread sheet. Sorry I wasn't more specific before. I do appreciate all
the
help.

Thank again,

Peter


"Roger Govier" wrote:

Hi Peter

One way
On sheet2 in F1 type 1996-01, in F2 1996-02 etc. for all of the
Year-month ranges you are interested in.
In F2
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=F1))
and copy down

Alternatively, if you want to put Excel dates in column F of sheet2,
then
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=TEX(F1,"yyyy-mm")))

--
Regards

Roger Govier


"Peter" wrote in message
...
In sheet 2 I'm creating at a glance information from the main sheet
1.
In
column F is my dates, Example is 6/15/2006. I would like to create
a
formula
that will tell me how many cells have January 2006, February 2006,
etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete

Adding months
 

Peter

Is immaterial where you enter the list of the 12 months that you wish to
summarise by.
Make it A1 through A12 if you wish, but then change the formula to look
at the same reference.

Just try it.
It will total the number of entries in column C that have dates that are
within that month.

--
Regards

Roger Govier


"Peter" wrote in message
...
Roger

I'm not sure I understand "If F1 enter 01/01/06, in F2 enter 2/1/06
and
onward" as all the dates on Sheet1 are located in Column C starting at
Row 3
and ending at C329. For the year 1998 this is how the dates read;
1/21/1998
2/25/1998
3/9/1998
3/18/1998
3/25/1998
3/25/1998
4/1/1998
4/14/1998
4/27/1998
4/28/1998
5/8/1998
5/18/1998
5/29/1998
6/10/1998
6/18/1998
7/4/1998
7/22/1998
8/22/1998
8/26/1998
9/4/1998
9/5/1998
9/9/1998
9/12/1998
9/27/1998
9/30/1998
10/13/1998
10/27/1998
10/28/1998
11/6/1998
11/6/1998
11/23/1998
Is this helpful to you?

Thanks

Peter

"Roger Govier" wrote:

Hi Peter

If I am understanding you correctly, then you want all results
totalled
for the month of April, regardless of which year it was.
If that is the case, and with your new information as to where the
dates
are located, then
If F1 enter 01/01/06, in F2 enter 02/01/06 and onward for each of the
12
months (the Year is unimportant here, so 2006 is OK)
On Sheet2 in G1 enter
=SUMPRODUCT(--(Month(Sheet1!$C$3:$A$1000)=Month(F1)))
Copy the formula down through G2:G12

--
Regards

Roger Govier


"Peter" wrote in message
...
I'm so new to this I find it difficult to understand some of the
functions. I
did however make a few mistakes. Sheet1 has all of vital
information.
Column
C starting with Row 3 has all of the dates. The date format starts
with
5/17/1995 and ends in Row 329 with the date 12/1/2006. The
information
I
would like to extract to Sheet2 will tell me how many cases I
signed
in any
given month. As an example the month of April may show I signed 25
cases and
the month of May may show 15 cases. The information will be from
the
entire
period stating in 1995 and ending in 2006 or when the dates end in
the
spread sheet. Sorry I wasn't more specific before. I do appreciate
all
the
help.

Thank again,

Peter


"Roger Govier" wrote:

Hi Peter

One way
On sheet2 in F1 type 1996-01, in F2 1996-02 etc. for all of the
Year-month ranges you are interested in.
In F2
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=F1))
and copy down

Alternatively, if you want to put Excel dates in column F of
sheet2,
then
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$1000,"yyyy-mm")=TEX(F1,"yyyy-mm")))

--
Regards

Roger Govier


"Peter" wrote in message
...
In sheet 2 I'm creating at a glance information from the main
sheet
1.
In
column F is my dates, Example is 6/15/2006. I would like to
create
a
formula
that will tell me how many cells have January 2006, February
2006,
etc. all
the way to 1996. Can anyone help with this formula?

Thanks

Pete