Sum items in a lookup table.
 

I work for a bus company that has 240 different routes. I also have a table
in Excel that lists monthly ridership for each route. I use it as a lookup
table to get data for another report. The problem? There are 6 of the 240
routes that are served by more than one garage so they appear twice in the
lookup table and I would like to get the sum of the ridership for that
particular route from the table. Is this possible and if so how do I do it?

Sum items in a lookup table.
 

I gather you're using a lookup function now to find the ridership from a
given route. VLOOKUP will return (0 or) 1 value. If you use SUMIF instead,
you can get the sum from multiple matches. If, for instance, your route is
in column A and ridership in column B, =vlookup(route,A1:A240,2,0) would give
you ridership for the first match. =sumif(A1:A240,route,B1:B240) will give
the total ridership for each match in the table.
--Bruce

"JICDB" wrote:

I work for a bus company that has 240 different routes. I also have a table
in Excel that lists monthly ridership for each route. I use it as a lookup
table to get data for another report. The problem? There are 6 of the 240
routes that are served by more than one garage so they appear twice in the
lookup table and I would like to get the sum of the ridership for that
particular route from the table. Is this possible and if so how do I do it?

Sum items in a lookup table.
 

Use SUMPRODUCT.

=SUMPRODUCT(--(A1:A240=1),(B1:B240))

A1:A240 are your routes, B1:B240 is your ridership. 1 represents the
route. If your route is text, just be sure to enclose in quotes.

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=496927