Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a formula to count items in a column if they are within cor
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 -- Josie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a formula to count items in a column if they are withincor
Josie wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
Can you count items in a column by color? | Excel Discussion (Misc queries) | |||
SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
How do I count the items in one column if another column is blank | Excel Worksheet Functions | |||
How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions |