Pivot table - SUM of COUNTS?

Here's the premise:

Vehicle crashes have stored with them many fields of data. Each row of my table contains (for example) location, crash type, speed, year occurred, vehicle type and details on up to four drivers/pedestrians involved (by a set of columns that repeat four times). Each driver's age is recorded in a column called AGE with values ranging from 0 to 900 or left blank depending on how many drivers were involced.

Here's a rudimentary example of what it looks like using commas as column breaks (you'll have to use your imagination):

YR,AGE,AGE,AGE,AGE
2005,18,22
2001,16,64,28,27
2008,78
1997,35,12

So I have four AGE columns displaying ages of drivers. In a pivot table, I want to SUM the COUNTS of each age within all of the four columns by the year the crash occurred. So the table has to be smart enough to know that for every crash that occurred in 2005, XX drivers (of up to four possible drivers) were XX years old.

As it stands so far, I can get a pivot table (and subsequent graph) to show me a multi-year breakdown per driver column by age (AGE, AGE2, AGE3, AGE4) or even by age groups that I determine (AGE5), but I can't get it to combine the counts from each AGE column into a singular column representing that particular age.

The icing on the cake would be to be able to define my own age groups as well using preset criteria (less than, greater than, etc.) because the age data can be so variable.

I hope I supplied enough info.

You can do it quite easily. Assume the spreadsheet with the data is named "Age1.xls" and the data in in a worksheet names "1".

With Age1.xls closed, in a new worksheet create a pivot table and define the source as external data. Choose the Age1.xls as the data source.

MSquery should the display the columns available as Yr, Age, Age1, Age2, Age3. Age1, Age2 and Age3 are the second, third and fourth columns called "Age".

Select Yr and Age into the query.
Click on the SQL button and edit the sql code to add the bolded lines.

SELECT `'1$'`.YR, `'1$'`.AGE
FROM `C:\Age1`.`'1$'` `'1$'`
union all
SELECT `'1$'`.YR, `'1$'`.AGE1
FROM `C:\Age1`.`'1$'` `'1$'`
union all
SELECT `'1$'`.YR, `'1$'`.AGE2
FROM `C:\Age1`.`'1$'` `'1$'`
union all
SELECT `'1$'`.YR, `'1$'`.AGE3
FROM `C:\Age1`.`'1$'` `'1$'`

The query converts each row of Year with 4 Age values to 4 rows each with a year and a single Age value. You can then easily create your pivot table and group as you wish.

regards