Subtract one column from another while matching client name and date

    I'm trying to write a formula that can subtract one column from another while taking into account multiple criteria (client name and date) since the rows in the columns do not line up. Essentially, I am trying to compare the Cumulative Sales amount from the end of last month to the Cumulative Sales amount from the same report pulled today for each client. I have attached a sample spreadsheet as well to better illustrate. Thank you!
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 315, colspan: 4"][SIZE=8px]END OF LAST MONTH[/SIZE][/TD]
    [TD="width: 27"] [/TD]
    [TD="width: 313, colspan: 4"][SIZE=8px]MONTH-TO-DATE[/SIZE][/TD]
    [TD="width: 30"] [/TD]
    [TD="width: 92"] [/TD]
    [TD="width: 26"] [/TD]
    [TD="width: 190"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    [SIZE=8px]Cumulative[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    [SIZE=8px]Cumulative[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Client[/SIZE]

    [/td]


    [td]

    [SIZE=8px]Month[/SIZE]

    [/td]


    [td]

    [SIZE=8px]Total Value $[/SIZE]

    [/td]


    [td]

    [SIZE=8px]$ Sales[/SIZE]

    [/td]


    [td][/td]


    [td]

    [SIZE=8px]Client[/SIZE]

    [/td]


    [td]

    [SIZE=8px]Month[/SIZE]

    [/td]


    [td]

    [SIZE=8px]Total Value $[/SIZE]

    [/td]


    [td]

    [SIZE=8px]$ Sales[/SIZE]

    [/td]


    [td][/td]


    [td]

    [SIZE=8px]MTD Sales[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Dec-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$467,822.77[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$1,509.52[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Feb-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jan-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$8,468,384.04[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$176,220.80[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Mar-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Feb-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$348,637.28[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$37,586.88[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Apr-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Mar-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$1,420,134.05[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$52,156.03[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]May-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Apr-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$4,747,310.52[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$153,970.17[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jun-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]May-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$4,855,414.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$244,408.09[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jul-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: left"] [TABLE="cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 43, align: right"][SIZE=8px]Jun-09[/SIZE][/TD]

    [/tr]


    [/TABLE]
    [/TD]
    [TD="align: right"][SIZE=8px]$2,278,476.57[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$91,687.02[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Aug-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=10px]Jul-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$7,586,268.53[/SIZE][/TD]
    [TD="align: right"][SIZE=10px]$343,676.64[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Sep-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Aug-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$6,491,043.02[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$292,106.71[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Oct-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Sep-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$11,198,413.23[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$335,428.04[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Nov-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]N/A[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Oct-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$7,696,023.34[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$339,896.41[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Dec-08[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$467,822.77[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$1,509.52[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]$0.00[/SIZE]

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Nov-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$7,289,044.85[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$353,909.07[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jan-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$8,468,384.04[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$176,220.80[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Dec-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$10,475,084.15[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$337,676.23[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Feb-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$348,637.28[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$37,586.88[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jan-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$10,874,495.91[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$494,469.19[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Mar-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$1,420,134.05[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$52,156.03[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Feb-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$10,815,212.87[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$407,795.75[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Apr-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$4,747,310.52[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$153,970.17[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Mar-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$19,726,852.92[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$574,868.66[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]May-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$4,855,414.00[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$244,408.09[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Need formula for "MTD Sales"[/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Apr-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$11,612,756.75[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$464,359.96[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jun-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$2,278,476.57[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$91,687.02[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]that subtracts col "D" from col "I"[/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]May-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$19,022,577.83[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$665,844.16[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=10px]Jul-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$7,586,268.53[/SIZE][/TD]
    [TD="align: right"][SIZE=10px]$343,701.64[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=10px]$25.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]to calculate this month's sold [/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jun-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$11,402,868.39[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$395,700.68[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Aug-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$6,491,043.02[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$292,106.71[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]amount while matching client[/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Jul-10[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$11,060,440.15[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$328,009.04[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]Company1[/SIZE]

    [/td]


    [TD="align: right"][SIZE=8px]Sep-09[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$11,198,413.23[/SIZE][/TD]
    [TD="align: right"][SIZE=8px]$335,428.04[/SIZE][/TD]

    [td][/td]


    [TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

    [td][/td]


    [td]

    [SIZE=8px]and month[/SIZE]

    [/td]


    [/tr]


    [/TABLE]

    Hello,


    A sample workbook would most probably allow the different contributors to help you out ...:wink:


    Sadly an image remains a totally inert object...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from Jaffey

    Carim, I uploaded the spreadsheet after your initial reply. Thanks!


    Hello,


    Do not know why my previous message got duplicated ...


    Just amended it to properly answer your initial question ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login