Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table sort entries that don't yet appear in table | Excel Discussion (Misc queries) | |||
Lookup table? | Excel Discussion (Misc queries) | |||
Disappearing Data Items on Pivot Table -Updated Question | Excel Discussion (Misc queries) | |||
lookup or table, not sure what to do | Excel Worksheet Functions | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |