counting occurrences in a given month
Posted by keke on July 26, 2001 12:13 PM
My workbook has three sheets: one for raw data on printing jobs and two for analysis. The 'workflow' sheet has headings like description, date quoted, quote number, date docketed, docket number, etc. Jobs are entered as they come in for quoting or production.
I need to set up a formula that automatically tells me how many items we quoted in each month and how many jobs we took in (docketed). The dates are formatted like "7/26/01". I came up with =COUNTIF('workflow!'F:F,"7") where column F is the date but it does not seem to read the date correctly.
Can you help? I've gone thru the archives but haven't found anything about counting occurrences of a date.
Thanks, keke
Posted by Mark W. on July 26, 2001 12:24 PM
How about using PivotTables...
...interested?
Posted by Aladin Akyurek on July 26, 2001 12:44 PM
Re: How about using PivotTables...
> ...interested?
Your question blocked me to give one of those formulas. So, I for one, yes.
Aladin
Posted by keke on July 26, 2001 12:56 PM
Re: How about using PivotTables...
Sure, if it'll work. Keep in mind I've never used a Pivot Table before.
keke
Posted by Mark W. on July 26, 2001 1:32 PM
...and, Here we go...
For discussion purposes let's suppose that your
data consists of 'date quoted' only, and cells
A1:A6 contain...
"date quoted"
4/13/01
6/30/01
7/5/01
7/13/01
7/26/01
1. With cell A1 selected choose the
Data | PivotTable Report... menu command
2. Press [ Next> ] at Steps 1 and 2 of 4.
3. Drag the 'date quoted' field button to the
ROW area of the white layout in the center of
the Step 3 of 4 dialog.
4. Drag a 2nd copy of 'date quoted' to the DATA
area of the same layout, and press [ Finish ].
5. Click on the 'date quoted' field button of the
newly created PivotTable, and choose the
Data | Group and Outline | Group... menu command.
6. You'll notice that "Months" is already highlighted
in the "By" list of the Grouping dialog. I recommend
that you select "Years" as well, and press [ OK ].
7. There's your summary. I purposely excluded
May data from the sample data. If you want May
to appear in the PivotTable as well double-click
the 'date quoted' field, and check the "Show
items with no data" check box. You can also
selectively hide any month by highlighting it in
the "Hide items" list.
Posted by keke on July 27, 2001 4:39 PM
Re: ...and, Here we go...
Wow, that worked great. Thank you! Even though my copy of Excel 2000 didn't follow exactly the same steps, it was close enough that I could figure it out.
One more question: can I have one Pivot Table that covers both 'date quoted' and 'date docketed' data, or must I keep two tables?
keke
