Summing values within a matrix who are both in a column where theheaders are within a certain numeri

[Andy]

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!

 

[barry houdini]

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

 

[Domenic]

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.

 

[Andy]

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

 

[Andy]

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.

Dear Domenic,

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

Andy