Announcement

Collapse
No announcement yet.

Find the Difference of a Matching Pair

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find the Difference of a Matching Pair



    I have the data below where an Opening and a Closing for same activity has the same Ticket Number. My desire is to find the difference of the multiple matching tickets by taking Closing minus Opening reading and summing them for the different types.
    Date Time Event Type Reading Ticket No. Desired Results
    05/02/2018 10:00:00 Opening A 1000 20 Type Total
    05/02/2018 12:30:00 Closing A 1500 20 A 600
    05/02/2018 13:00:00 Opening C 200 21 B 400
    05/02/2018 15:30:00 Closing C 500 21 C 2300
    06/02/2018 14:35:00 Opening A 600 22 Total 3300
    06/02/2018 17:00:00 Closing A 700 22
    07/02/2018 11:50:00 Opening B 800 23
    07/02/2018 14:15:00 Closing B 1200 23
    11/02/2018 12:00:00 Opening C 3000 24
    11/02/2018 15:45:00 Closing C 5000 24

  • #2
    Assuming date in columns A:F and your first result in I3, try:

    =SUMIFS($E$2:$E$11,$D$2:$D$11,H3,$C$2:$C$11,"closing")-SUMIFS($E$2:$E$11,$D$2:$D$11,H3,$C$2:$C$11,"Opening")

    copied down
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3
      Thanks NBVC for your reply.
      Although the formulae works, it is not quite what I was looking for. I had hoped of getting one which uses the Ticket No. as a criteria to find the partner.

      Comment


      • #4
        Originally posted by NBVC View Post
        Assuming date in columns A:F and your first result in I3, try:

        =SUMIFS($E$2:$E$11,$D$2:$D$11,H3,$C$2:$C$11,"closing")-SUMIFS($E$2:$E$11,$D$2:$D$11,H3,$C$2:$C$11,"Opening")

        copied down
        Sorry, I tried it a few times and didn't succeed.

        Comment


        • #5
          Originally posted by rtuwey View Post
          Thanks NBVC for your reply.
          Although the formulae works, it is not quite what I was looking for. I had hoped of getting one which uses the Ticket No. as a criteria to find the partner.
          Can you explain or show how we would use the Ticket No.? I only see evidence of the Ticket No. in the Main table on the left.
          Where there is a will there are many ways. Finding one that works for you is the challenge!

          MS Excel MVP 2010-2016

          Comment


          • #6
            For every Ticket No, there is an opening and a closing figure. What I need is a formula to look for both matching tickets and subtract opening from the closing reading, then sum all similar Types in the range.

            Comment


            • #7


              Can you give a sample of when my formula would not work? I am not understanding why it is important to include ticket number if the formula.
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment

              Working...
              X