Excel 2007 - Reports

[fubak]

I'm having a problem with creating a chart that uses the data how I want it to. Here's my data...

Spreadsheet 1:
CatID Category
1 Cat1
2 Cat2
3 Cat3

Spreadsheet 2:
CatID Date
1 7/16/2009
2 7/16/2009
3 7/17/2009
1 7/20/2009
2 7/22/2009
3 7/25/2009
2 7/26/2009
3 7/12/2009
1 7/28/2009
3 7/20/2009

I want to create a Pie Chart that shows how many times each category occurs in the CatID column of Spreadsheet 2. So the Chart would show Cat 1 occurs 3 times, Cat 2 occurs 3 times, and Cat 3 occurs 4 times.

I'm a noob when it comes to Excel reporting and I'm having a tough time with it. Please help! Thanks!

 

[fubak]

bump

 

[arkamw]

I would (very likely long and involved) use a countif statement.

So, in spreadsheet one, it looks like you already have spaces for Cat1, Cat2, and Cat3 in column A. So, in the corresponding rows in column B you could use something like:

=COUNTIF([spreadsheet2.xls]Sheet1!$A$1:$A$65536,"=1")

altering the column and range, and =1 part for the different categories and the total range that fits your needs. I'm assuming that the category is based on the date and you aren't necessarily wanting the date counted (though that wouldn't be too much extra trouble).

Then, just create the pie chart based on the data that's in spreadsheet one and Bob's your uncle. Just tried this out and it works great though there is probably an easier, less kludgy, way of going about it.