Excel Help - graphs

[TonyHetherington]

Quick question for the excel gurus;

I have a list of data next to which is a date. So for exmaple

10/12/2003 - UK
01/02/2004 - Sweden
01/02/2004 - Sweden
01/02/2004 - Sweden


etc. etc. (approx 500 lines). Now, the data is to actual DATE but I want to create a graph which is sorted by number per month. So I want a graph, for sweden, that shows how many per month (even though it's sorted by date).

How do I do a simple count per month, given that I have the actual date?

Thanks!

[slinky]

would a =countif statement do the job?

Then graph based on the output of that statement?

slinky

[buzzsaw]

TonyHetherington said:

Quick question for the excel gurus;

I have a list of data next to which is a date. So for exmaple

10/12/2003 - UK
01/02/2004 - Sweden
01/02/2004 - Sweden
01/02/2004 - Sweden


etc. etc. (approx 500 lines). Now, the data is to actual DATE but I want to create a graph which is sorted by number per month. So I want a graph, for sweden, that shows how many per month (even though it's sorted by date).

How do I do a simple count per month, given that I have the actual date?

Thanks!

I use a similar spreadsheet which graphs number of returns per month.

I do this by using MID function to pull month figure from date in separate column, then the COUNTIF function to count how many occurrences per month.

[TonyHetherington]

I thought about that but couldn't get it to work; 4 yrs of data you use what 'countif'...i'd have to do that for every month and would defeat the object i think

[Jinx]

Subtotals; use function "Count"?

Edited by Jinx on Wednesday 7th June 13:01

[victormeldrew]

assuming you have a column with date in col a:

add a new column with formula =month(a2) in row 2, and copy down the column

now create a pivotchart. stick the country in the page box, month in the row box, and count of row in the data box.


Ooops - put =year(a2)&"/"&month(a2) as your formula, it will work better!

Edited by victormeldrew on Wednesday 7th June 13:05

[TonyHetherington]

buzzsaw said:

I use a similar spreadsheet which graphs number of returns per month.

I do this by using MID function to pull month figure from date in separate column, then the COUNTIF function to count how many occurrences per month.

ahhh, top man!! Thanks!

[TonyHetherington]

victormeldrew said:

add a new column with formula =month(a2) in row 2, and copy down the column

genius! Even better...that's exactly it. Thanks!!!

[victormeldrew]

put =year(a2)&"/"&month(a2) as your formula, it will work better!

[TonyHetherington]