On Nov 7, 4:03*am, Domenic wrote:
Assumptions:

For the source table...

A2:A10 contains the row headers/category

B1:E1 contains the column headers/length of trip

B2:E10 contains the data/number of trips

For the results table...

G3 contains the first of the combination of categories, such as 'Work
Trips' (needs to match the categories listed in A2:A10)

H3 contains the second of the combination of categories, such as
'Shopping Trips' (needs to match the categories listed in A2:A10)

And so on for the rest of Column G and Column H

I1 contains 1 and I2 contains 25

J1 contains 26 and J2 contains 50

And so on for other ranges

Formula:

I3, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=SUM(IF(ISNUMBER(MATCH($A$2:$A$10,$G3:$H3,0)),IF($ B$1:$E$1=I$1,IF($B$1:$
E$1<=I$2,$B$1:$E$1*$B$2:$E$10))))

Adjust the ranges, accordingly.

--
Domenic
Microsoft Excel MVPwww.xl-central.com
Your Quick Reference to Excel Solutions

In article
,

*Andy wrote:
I have a matrix of car trip data where the row headings are categories
(such as "work trips", "shopping trips" ...) and the column headings
are lengths of the trip in miles (e.g. 1, 2, 3, 4...). *The cell
values in the matrix are the number of trips of a particular distance
and within a particular category.

I wish to summarize the data in a table where the column headers are
ranges (e.g. 1-25, 26-50 ...) and the row headers are combined (e.g.
work+school, shopping+leisure, ...). *The cell values in this summary
table are to be total miles traveled, i.e. a sumproduct of the trip
length multiplied by the number of trips for the particular distance
range and category range.

What is the formula for the cells within this summary table?

Thanks!

Dear Domenic,

This is also a great solution and I've tried it and it works great.
Thanks for your help!

Andy