I'm creating a spreadsheet in which I want to enter data and have the date
entered be used to separate the information so I can total it. For instance,
all entries made in January are totaled, and February, etc. So that I can
produce a monthly report and then a yearly total. I have been trying to
figure this out for days!

Yes, the date is in the cell, like 1/1/07, but when I look at formulas, it
has a number that the program assigned to the cell

"bj" wrote:

do you have cells with the entry date?
if so, what is entered?

"cindy" wrote:

I'm creating a spreadsheet in which I want to enter data and have the date
entered be used to separate the information so I can total it. For instance,
all entries made in January are totaled, and February, etc. So that I can
produce a monthly report and then a yearly total. I have been trying to
figure this out for days!

I'm creating a spreadsheet in which I want to enter data and have
the date entered be used to separate the information so I can total
it. For instance, all entries made in January are totaled, and
February, etc. So that I can produce a monthly report and then a
yearly total. I have been trying to figure this out for days!


do you have cells with the entry date?
if so, what is entered?

Yes, the date is in the cell, like 1/1/07, but when I look at
formulas, it has a number that the program assigned to the cell

Probably what's happening is that the "number" is the numerical
representation of the formatted date.

If the dates are in column A and the numbers to be summed are in column C,
one approach is to put =MONTH(A1) in B1 and copy down to the end of the
data, then put =SUMIF(B:B,ROW(),C:C) in H1 and copy down to H12 (for 12
months).

Modify to suit. Hope this works.

It didn't work. Perhaps it would help if I put some of the columns for you
to look at.
Column A states the marital status, Column B has date of visit, Column C is
empty (to put in the formula I need) Column D has a zero or a one depending
on whether the criteria in Column A is met. For instance, Column D is a 1
only if Column A is "Single", Column E is a 1 only if Column A is "Married",
Column F is a 1 only if Column A is "Divorced", etc. If the criteria for
Column A is not met, then a zero is entered in the other columns. I need a
formula that looks at Column B, decides what month each visit was in, and
then adds the total number of 1's in each column that falls in that month.
In the end I need all 12 months listed with the total number of Single,
Married or Divorced in each month.

I sure hope this helps and really appreciate your trying to help me.


"MyVeryOwnSelf" wrote:

I'm creating a spreadsheet in which I want to enter data and have
the date entered be used to separate the information so I can total
it. For instance, all entries made in January are totaled, and
February, etc. So that I can produce a monthly report and then a
yearly total. I have been trying to figure this out for days!


do you have cells with the entry date?
if so, what is entered?

Yes, the date is in the cell, like 1/1/07, but when I look at
formulas, it has a number that the program assigned to the cell

Probably what's happening is that the "number" is the numerical
representation of the formatted date.

If the dates are in column A and the numbers to be summed are in column C,
one approach is to put =MONTH(A1) in B1 and copy down to the end of the
data, then put =SUMIF(B:B,ROW(),C:C) in H1 and copy down to H12 (for 12
months).

Modify to suit. Hope this works.

Hi Cindy

You only need columns A and B.
Mark your range of data in columns A and B, DataPivot TableFinish
On the new sheet that appears, with the PT skeleton
drag Date from the Field List to the Row area
drag Status from the Field List to the Column Area
drag Status again from the Field List to the Data area
Right click on DateGroup and OutlineGroupchoose Month and Year

There, you have your report.

--
Regards

Roger Govier


"cindy" wrote in message
...
It didn't work. Perhaps it would help if I put some of the columns
for you
to look at.
Column A states the marital status, Column B has date of visit, Column
C is
empty (to put in the formula I need) Column D has a zero or a one
depending
on whether the criteria in Column A is met. For instance, Column D is
a 1
only if Column A is "Single", Column E is a 1 only if Column A is
"Married",
Column F is a 1 only if Column A is "Divorced", etc. If the criteria
for
Column A is not met, then a zero is entered in the other columns. I
need a
formula that looks at Column B, decides what month each visit was in,
and
then adds the total number of 1's in each column that falls in that
month.
In the end I need all 12 months listed with the total number of
Single,
Married or Divorced in each month.

I sure hope this helps and really appreciate your trying to help me.


"MyVeryOwnSelf" wrote:

I'm creating a spreadsheet in which I want to enter data and
have
the date entered be used to separate the information so I can
total
it. For instance, all entries made in January are totaled, and
February, etc. So that I can produce a monthly report and then
a
yearly total. I have been trying to figure this out for days!


do you have cells with the entry date?
if so, what is entered?

Yes, the date is in the cell, like 1/1/07, but when I look at
formulas, it has a number that the program assigned to the cell

Probably what's happening is that the "number" is the numerical
representation of the formatted date.

If the dates are in column A and the numbers to be summed are in
column C,
one approach is to put =MONTH(A1) in B1 and copy down to the end of
the
data, then put =SUMIF(B:B,ROW(),C:C) in H1 and copy down to H12 (for
12
months).

Modify to suit. Hope this works.

Thank you! Thank you! Thank you! You have saved my sanity and my boss is
grateful I didn't quit over this!

"Roger Govier" wrote:

Hi Cindy

You only need columns A and B.
Mark your range of data in columns A and B, DataPivot TableFinish
On the new sheet that appears, with the PT skeleton
drag Date from the Field List to the Row area
drag Status from the Field List to the Column Area
drag Status again from the Field List to the Data area
Right click on DateGroup and OutlineGroupchoose Month and Year

There, you have your report.

--
Regards

Roger Govier


"cindy" wrote in message
...
It didn't work. Perhaps it would help if I put some of the columns
for you
to look at.
Column A states the marital status, Column B has date of visit, Column
C is
empty (to put in the formula I need) Column D has a zero or a one
depending
on whether the criteria in Column A is met. For instance, Column D is
a 1
only if Column A is "Single", Column E is a 1 only if Column A is
"Married",
Column F is a 1 only if Column A is "Divorced", etc. If the criteria
for
Column A is not met, then a zero is entered in the other columns. I
need a
formula that looks at Column B, decides what month each visit was in,
and
then adds the total number of 1's in each column that falls in that
month.
In the end I need all 12 months listed with the total number of
Single,
Married or Divorced in each month.

I sure hope this helps and really appreciate your trying to help me.


"MyVeryOwnSelf" wrote:

I'm creating a spreadsheet in which I want to enter data and
have
the date entered be used to separate the information so I can
total
it. For instance, all entries made in January are totaled, and
February, etc. So that I can produce a monthly report and then
a
yearly total. I have been trying to figure this out for days!


do you have cells with the entry date?
if so, what is entered?

Yes, the date is in the cell, like 1/1/07, but when I look at
formulas, it has a number that the program assigned to the cell

Probably what's happening is that the "number" is the numerical
representation of the formatted date.

If the dates are in column A and the numbers to be summed are in
column C,
one approach is to put =MONTH(A1) in B1 and copy down to the end of
the
data, then put =SUMIF(B:B,ROW(),C:C) in H1 and copy down to H12 (for
12
months).

Modify to suit. Hope this works.

Who knows, he might even give you a pay rise!!!<g

--
Regards

Roger Govier


"cindy" wrote in message
...
Thank you! Thank you! Thank you! You have saved my sanity and my boss
is
grateful I didn't quit over this!

"Roger Govier" wrote:

Hi Cindy

You only need columns A and B.
Mark your range of data in columns A and B, DataPivot TableFinish
On the new sheet that appears, with the PT skeleton
drag Date from the Field List to the Row area
drag Status from the Field List to the Column Area
drag Status again from the Field List to the Data area
Right click on DateGroup and OutlineGroupchoose Month and Year

There, you have your report.

--
Regards

Roger Govier


"cindy" wrote in message
...
It didn't work. Perhaps it would help if I put some of the columns
for you
to look at.
Column A states the marital status, Column B has date of visit,
Column
C is
empty (to put in the formula I need) Column D has a zero or a one
depending
on whether the criteria in Column A is met. For instance, Column D
is
a 1
only if Column A is "Single", Column E is a 1 only if Column A is
"Married",
Column F is a 1 only if Column A is "Divorced", etc. If the
criteria
for
Column A is not met, then a zero is entered in the other columns.
I
need a
formula that looks at Column B, decides what month each visit was
in,
and
then adds the total number of 1's in each column that falls in that
month.
In the end I need all 12 months listed with the total number of
Single,
Married or Divorced in each month.

I sure hope this helps and really appreciate your trying to help
me.


"MyVeryOwnSelf" wrote:

I'm creating a spreadsheet in which I want to enter data and
have
the date entered be used to separate the information so I can
total
it. For instance, all entries made in January are totaled,
and
February, etc. So that I can produce a monthly report and
then
a
yearly total. I have been trying to figure this out for
days!


do you have cells with the entry date?
if so, what is entered?

Yes, the date is in the cell, like 1/1/07, but when I look at
formulas, it has a number that the program assigned to the cell

Probably what's happening is that the "number" is the numerical
representation of the formatted date.

If the dates are in column A and the numbers to be summed are in
column C,
one approach is to put =MONTH(A1) in B1 and copy down to the end
of
the
data, then put =SUMIF(B:B,ROW(),C:C) in H1 and copy down to H12
(for
12
months).

Modify to suit. Hope this works.