special charts in excel

[wojak]

Does anyone know how to create a special chart that would create a graph of two fields based on number of counts of one of those fields (zip code)?
I need to create a chart that would graph sales based on the zip code.
This is the way the excel sheet is setup.

Date service amount zip code
1 100 60068
2 125 60630
3 205 60616
4 235 60068
4 200 60630

Date is the date of service and there can be multiple service lines for the same date.

The totals in this chart would be based on zip codes and the graph would also be based on zip codes.
The sheet is also setup to calculate totals for the month and than there is another sheet that adds the count for the year.
The graph would most likely need to add fields for the month and than for the year.


Zip code
60068 100+235=335
60616 205
60630 125+200=325


What I need is a graph that would add the service amount and graph them based on the zip codes so I would know how much business I get from each zip code.

Any ideas
Thanks for all your help


Robert

 

[Bglad]

First, post this in the software section.

The real way to do this is by putting your info into an Access database rather than Excel. Then you could sort by zip and export sales data into excel for graphing. Given that you are already in excel...

Create an extra worksheet for scratch work for your graph. You will have to manually put in each zip code in say your topmost row (database would alleviate this problem). Then use the next row to total the sales for that zip with the "sumif" function.

Sumif uses three variables:
1. range of cells to be evaluated or the cells containing your zip codes in the main page
2. criteria to be evaluated which would be the A row of that column on your scratch page telling it to sum for that zip code
3. sum range which would be the sales figures to be summed from your main page

Graph the info from your scratch worksheet. You can graph from your mainpage and link to data on the scratch worksheet or vice versa if you want.

If you don't have sumif you need to install the conditional sum wizard by going to tools > add ins.