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

        Working...
        X