On Nov 7, 3:40*am, barry houdini wrote:
On Nov 7, 12:03*am, 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!

Hello Andy,

If the matrix row headers are in A2:A20 and column headers in B1:Z1,
with data in B2:Z20 then you can use a formula like this to calculate
total mileage of work trips and shopping trips in the range 5 to 10
miles inclusive

=SUMPRODUCT(ISNUMBER(MATCH(A2:A20,{"work trips","shopping trips"},0))*
(B1:Z1=5)*(B1:Z1<=10)*B2:Z20*B1:Z1)

obviously you could replace the variables with cell references
depending on the setup of your table

Dear Barry,

This solution works perfectly and will save me a LOT of time! Thanks
very much for your time and help!

Andy