Need to know what formula to use

C

cindy

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!
 
C

cindy

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
 
M

MyVeryOwnSelf

I'm creating a spreadsheet in which I want to enter data and have
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
Click to expand...

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.
 
C

cindy

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.
 
R

Roger Govier

Hi Cindy

You only need columns A and B.
Mark your range of data in columns A and B, Data>Pivot Table>Finish
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 Date>Group and Outline>Group>choose Month and Year

There, you have your report.
 
C

cindy

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