Charting based on a specific month in a column

  • Thread starter Thread starter Mas
  • Start date Start date
M

Mas

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.
 
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 D2:D3 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
 
Back
Top