If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charting based on a specific month in a column
Hi all,
I would like to plot a bar chart that plots the bar based on the sum of a column for a particular month. For example: A1 Date A2 1/5/09 A3 2/5/09 A4 1/6/09 B1 Qty B2 10 B3 10 B4 5 I would like to plot a bar chart that on the bottom axis has the months i.e. May, June and then sums up column B and plots the sum of the figures against the months i.e. May - Qty 20, June - Qty 5 etc. I appreciate any assistance you can give me. |
#2
|
|||
|
|||
Charting based on a specific month in a column
In D1 enter text "date" in E1 enter text "Qty"
In D2 enter date for Jan 1 (1/1/2009) and in D3 date for 1 Feb 2009 (looks like you would use 1/2/2009 but if USA then use 2/1/2009) Select D23 and drag down to D13. Now you have date fro the first of each month in the year In E2 enter this formula =SUMPRODUCT(--(MONTH(D2)=MONTH($A$2:$A$20)),$B$2:$B$20) but change the 20 to reflect the last cell in your actual data Copy this formula down the column (double click the fill handle) The result is a table with months in column D and quantities in column E Make you chart form this data You many want to format the x-axis as it will come out with entries like "Jan-2009" and you may want only "Jan" --- use a custom format of "mmm" best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mas" wrote in message ... Hi all, I would like to plot a bar chart that plots the bar based on the sum of a column for a particular month. For example: A1 Date A2 1/5/09 A3 2/5/09 A4 1/6/09 B1 Qty B2 10 B3 10 B4 5 I would like to plot a bar chart that on the bottom axis has the months i.e. May, June and then sums up column B and plots the sum of the figures against the months i.e. May - Qty 20, June - Qty 5 etc. I appreciate any assistance you can give me. |
Thread Tools | |
Display Modes | |
|
|