Adding Exceptions to Sumif VBA Calculations

[snuffnchess]

Previously posted at Adding Exceptions to Sumif VBA Calculations but no recent replies so posting here

Have a set of data that calculations are done off of that is pretty straight forward except for when it comes to exceptions.

Essentially what is happening is that a customer will have a special price for a specific product if it falls into a verrrrry narrow category. So we need to calculate rates for the product normally when it does not fall into that category separately from when it does.

Attached is a sample file with calculations.

    cdata.Activate
    cdatalr = Cells(Rows.Count, "A").End(xlUp).Row
    cdata.Range("C2:C" & cdatalr).AdvancedFilter Action:=xlFilterCopy, copytorange:=cur.Range("A2"), Unique:=True
    
    cur.Activate
    cur.Rows(2).Delete
    curlr = Cells(Rows.Count, "A").End(xlUp).Row
      
        With cur.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A1:A" & curlr), Order:=xlAscending
            .SetRange Range("A1:A" & curlr)
            .Header = xlYes
            .Apply
        End With
    
    
    cur.Range("A1").Value = "Owner"
    cur.Range("B1").Value = "FT"
    cur.Range("C1").Value = "LT"
    cur.Range("D1").Value = "UT"
    cur.Range("E1").Value = "UTA"
    
    Set curn = cdata.Range("C2:C" & cdatalr)
    Set curf = cdata.Range("O2:O" & cdatalr)
    Set curl = cdata.Range("K2:K" & cdatalr)
    Set curu = cdata.Range("M2:M" & cdatalr)
    Set curact = cdata.Range("Q2:Q" & cdatalr)
    
    
    For i = 2 To curlr
        cur.Range("B" & i).Value = Application.WorksheetFunction.SumIf(curn, cur.Range("A" & i).Value, curf)
        cur.Range("C" & i).Value = Application.WorksheetFunction.SumIf(curn, cur.Range("A" & i).Value, curl)
        cur.Range("D" & i).Value = Application.WorksheetFunction.SumIf(curn, cur.Range("A" & i).Value, curu)
        cur.Range("E" & i).Value = Application.WorksheetFunction.SumIf(curn, cur.Range("A" & i).Value, curact)
    Next i

So the code here works if I do not take the exception into account. There are a few uncertainties here. There can be 0, 1, or many exceptions per owner., so those additional ones should appear to the right of the data set

There will be later calculations that are done based on the value that appears in the exception column. Right Now I am ONLY focused on splitting out the exceptions and looking at the Worksheet Data columns with -Mar in the header

In the case of the example, where the exception is on the Data Worksheet - Column FT-Mar data (and
(All FT-MAR Data for Buf904 = $3267.85. (Data - Column O sum).

However, there is an exemption listed for Owner Buf904, Account Name Yard55323. So what I want do to is pull whatever the sum of the Data Worksheet Column FT-Mar is and place it in Worksheet Final Column H (and place the Account Name and Account Number in columns F and G), and then to subtract whatever that value is from the previous sum of all of the FT-Mar Column O data.

This would need to happen for all of the exceptions with moving just one column to the right for each one that appears for that specific Owner.

Eventually there will be another Worksheet where the exemption customers use the % listed in Exemption Column E and multiply that times whatever would be in the Final Column H, K, N, etc.

So.... in the end, Final, B2 = $3267.85 (sum of all data in Data Column O) - 627.06 (Amount of Exemption account in Data Column O) = 2640.79

Does that make sense???

I was unable to get the XL2BB addin to work, so I could not add my data in here. I have posted a link to a workbook with sample information in it so that the data can be seen.

Loading Google Sheets

Let me know if you have any questions.