Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JICDB
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
SteveG
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM
Lookup table? Peter Excel Discussion (Misc queries) 1 September 17th 05 08:02 PM
Disappearing Data Items on Pivot Table -Updated Question GeorgeChe Excel Discussion (Misc queries) 4 August 2nd 05 03:22 PM
lookup or table, not sure what to do [email protected] Excel Worksheet Functions 2 February 17th 05 10:36 PM
Pivot Table - Group by Month - Show Items with no data Dan Reynolds Excel Discussion (Misc queries) 0 November 28th 04 02:01 AM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"