Find the Difference of a Matching Pair

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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. [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 439"]

    [tr]


    [TD="width: 73"]Date [/TD]
    [TD="width: 64"]Time[/TD]
    [TD="width: 64"]Event[/TD]
    [TD="width: 64"]Type[/TD]
    [TD="width: 64"]Reading[/TD]
    [TD="width: 64"]Ticket No.[/TD]
    [TD="width: 64"] [/TD]
    [TD="width: 128, colspan: 2"]Desired Results[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]05/02/2018[/TD]
    [TD="class: xl66, align: right"]10:00:00[/TD]

    [td]

    Opening

    [/td]


    [TD="class: xl67"]A[/TD]
    [TD="align: right"]1000[/TD]
    [TD="align: right"]20[/TD]

    [td][/td]


    [td]

    Type

    [/td]


    [td]

    Total

    [/td]


    [/tr]


    [tr]


    [TD="class: xl65"]05/02/2018[/TD]
    [TD="class: xl66, align: right"]12:30:00[/TD]

    [td]

    Closing

    [/td]


    [TD="class: xl67"]A[/TD]
    [TD="align: right"]1500[/TD]
    [TD="align: right"]20[/TD]

    [td][/td]


    [td]

    A

    [/td]


    [TD="align: right"]600[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]05/02/2018[/TD]
    [TD="class: xl66, align: right"]13:00:00[/TD]

    [td]

    Opening

    [/td]


    [TD="class: xl67"]C[/TD]
    [TD="align: right"]200[/TD]
    [TD="align: right"]21[/TD]

    [td][/td]


    [td]

    B

    [/td]


    [TD="align: right"]400[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]05/02/2018[/TD]
    [TD="class: xl66, align: right"]15:30:00[/TD]

    [td]

    Closing

    [/td]


    [TD="class: xl67"]C[/TD]
    [TD="align: right"]500[/TD]
    [TD="align: right"]21[/TD]

    [td][/td]


    [td]

    C

    [/td]


    [TD="align: right"]2300[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]06/02/2018[/TD]
    [TD="class: xl66, align: right"]14:35:00[/TD]

    [td]

    Opening

    [/td]


    [TD="class: xl67"]A[/TD]
    [TD="align: right"]600[/TD]
    [TD="align: right"]22[/TD]

    [td][/td]


    [td]

    Total

    [/td]


    [TD="align: right"]3300[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]06/02/2018[/TD]
    [TD="class: xl66, align: right"]17:00:00[/TD]

    [td]

    Closing

    [/td]


    [TD="class: xl67"]A[/TD]
    [TD="align: right"]700[/TD]
    [TD="align: right"]22[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65"]07/02/2018[/TD]
    [TD="class: xl66, align: right"]11:50:00[/TD]

    [td]

    Opening

    [/td]


    [TD="class: xl67"]B[/TD]
    [TD="align: right"]800[/TD]
    [TD="align: right"]23[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65"]07/02/2018[/TD]
    [TD="class: xl66, align: right"]14:15:00[/TD]

    [td]

    Closing

    [/td]


    [TD="class: xl67"]B[/TD]
    [TD="align: right"]1200[/TD]
    [TD="align: right"]23[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65"]11/02/2018[/TD]
    [TD="class: xl66, align: right"]12:00:00[/TD]

    [td]

    Opening

    [/td]


    [TD="class: xl67"]C[/TD]
    [TD="align: right"]3000[/TD]
    [TD="align: right"]24[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65"]11/02/2018[/TD]
    [TD="class: xl66, align: right"]15:45:00[/TD]

    [td]

    Closing

    [/td]


    [TD="class: xl67"]C[/TD]
    [TD="align: right"]5000[/TD]
    [TD="align: right"]24[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    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!

    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.

    Quote from NBVC

    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.:?

    Quote from rtuwey

    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!

    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.

    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login