+ Reply to Thread
Results 1 to 5 of 5

How to compare data in table and return sum of matching pairs?

  1. #1
    Registered User
    Join Date
    08-02-2007
    Posts
    3

    How to compare data in table and return sum of matching pairs?

    I’ve got a table with three columns: the first column (column A) shows bus service numbers, the second (column B) indicates the route direction (circular, inward, outward) and the third column (column C) the respective mileage. I would like to add the mileage for matching inward and outward services so I know how long the return journey would be (the routes can be slightly different due to one-way streets for example). Here is a short example (my table contains a lot more services):

    number direction mileage
    3A Circular 3.18
    5E Inward 9.47
    5E Outward 9.43
    5EB Outward 9.21
    6A Circular 5.57

    I think in a first step I’d need to exclude all circular services by sorting the data, which is no problem. Then I’d have to check if there is a matching pair in column A. Then, if there is such a pair I’d need to check if one of them is an inward and the other one an outward journey in column B. If this is the case too, I’d like to add up their individual mileages.

    Any help on all steps would be greatly appreciated.

    For the first step I tried MATCH but because the cell’s value is within the range I am comparing it to, the result is always true (e.g. MATCH(A3,A$1:A$5,0)) and of course MATCH only returns the position of the matched value but I suppose I could overcome the latter by using an IF function and turning any number into TRUE?

    For the other steps I don’t have any ideas at all.

    Maybe I am trying to achieve something that isn’t possible but I don’t hope so!

    Many thanks for your help!
    Chris

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Chris

    Assuming that your data as shown is in the range A1:C6 (including the headings) then

    D2:=IF(AND(B2<>"Circular",COUNTIF($A$2:$A$6,A2)=2,SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6="Inward"))=1,SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6="Outward"))=1),SUMPRODUCT(--($A$2:$A$6=A2),($C$2:$C$6)),"")

    Copy down to D6.

    Note there will be duplicates as the route will be shown twice. If you extracted a unique list of routes, put it somewhere else, you could reference that to get a single result for the total.


    rylo

  3. #3
    Registered User
    Join Date
    08-02-2007
    Posts
    3
    Rylo,

    Thanks a lot! This is exactly what I was looking for!

    Out of curiosity, could you tell me what exactly the SUMPRODUCT function is doing in this case and why it is set to equal 1? It seems to be doing something different to what is explained in MS Excel Help.

    Cheers,
    Chris

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

  5. #5
    Registered User
    Join Date
    08-02-2007
    Posts
    3
    Thanks a lot! The link was very helpful!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1