Assign a formula to count items in a column if they are within cor

[Josie]

I have a spreadsheet in Excel 2003 which contains the source data for a pie
chart on a separate sheet in the workbook showing number of different
categories that have occurred e.g. manual handling, transport , housekeeping
etc. These categories are counted using the formula
=COUNTIF(Sheet1!$F$2:$F$500,"Housekeeping") and the results are shown in a
table alongside the pie chart and used to generate the chart. The
spreadsheet contains data collected at intermittent times throughout the year
and the first column contains the date of that row's information. I would
like to be able to produce a monthly chart. How do I adjust the formula so it
only picks up data for each month. i have assumed that each month will have
its own chart in the workbook. the date can be formatted as required. At
the moment they go in as 1/1/09 for example but January could be used if
necessary

 

[smartin]

I have a spreadsheet in Excel 2003 which contains the source data for a pie
chart on a separate sheet in the workbook showing number of different
categories that have occurred e.g. manual handling, transport , housekeeping
etc. These categories are counted using the formula
=COUNTIF(Sheet1!$F$2:$F$500,"Housekeeping") and the results are shown in a
table alongside the pie chart and used to generate the chart. The
spreadsheet contains data collected at intermittent times throughout the year
and the first column contains the date of that row's information. I would
like to be able to produce a monthly chart. How do I adjust the formula so it
only picks up data for each month. i have assumed that each month will have
its own chart in the workbook. the date can be formatted as required. At
the moment they go in as 1/1/09 for example but January could be used if
necessary

Let's say Sheet2 is where you will query one month. In that sheet:

A1= "Month:"
B1= 1/1/2009 (enter a /date/)

A4= "Housekeeping"
A5= "Transport"
A6= etc.

B3= "Count"
B4
=SUMPRODUCT(--(MONTH(Sheet2!$B$1)=MONTH(Sheet1!$A$2:$A$500)),--(Sheet2!$A4=Sheet1!$F$2:$F$500))

Fill down B4 as needed. Make a chart. Preferably a bar chart (but make a
pie chart if you must). This is your chart for January.

If you want to make a chart for other months you can:

* change the date in B1, or
* copy Sheet2 to a new worksheet and then change the date in B1

If you have multiple years in your data we can modify the formula in B4
to be more specific. Post back if this is an issue.

Hope this helps.