Forum Discussion

MLT-CH's avatar
MLT-CH
Copper Contributor
Jun 30, 2023

SUMIFS Function to calculate quarterly different VAT rates

Hello Community, 

I have the following excel sheet. I need a formula that will calculate the sum of each VAT rate per category (e.g. Materials Q1/22 7.7%, Material Q1/22 2.5%, Materials Q1/22 0% etc. per category.

I can't seem to get a correct formula to produce this report.

Thank you.

Report IDMerchantCategoryTotalVATVAT paidDescriptionDate
Expense Report #4 MaterialsCHF44.530% 0 31-12-22
Expense Report #4 MaterialsCHF32.207.7% 2.3 30-12-22
Expense Report #4 MaterialsCHF14.750% 0 30-12-22
Expense Report #4 MaterialsCHF19.230% 0 30-12-22
Expense Report #4 Representation expensesCHF8.902.5% 0.22 30-12-22
Expense Report #4 MaterialsCHF82.157.7% 5.87 29-12-22
  • erictribble's avatar
    erictribble
    Copper Contributor

    MLT-CH Its a bit of a messy formula but here it is.

    First get the Quarter and Year in its own column. (since your date format is dd-mm-yy, I had to make it a bit long so you can keep your format)

    "Q"&INT((MONTH(DATE("20"&RIGHT(Table1[@Date],2),MID(Table1[@Date],4,2),LEFT(Table1[@Date],2)))+2)/3)&"-20"&RIGHT(Table1[@Date],2)

     

    Next column get the calculations in the format needed (again a bit long)

    =Table1[@Category]&" "&"Q"&INT((MONTH(DATE("20"&RIGHT(Table1[@Date],2),MID(Table1[@Date],4,2),LEFT(Table1[@Date],2)))+2)/3)&"/"&TEXT(SUMIFS(Table1[VAT],Table1[Category],Table1[@Category],[QTR-YEAR],[@[QTR-YEAR]]),"0.00%")

     

    Then pull the uniques from the calculations column to get your end values.

    =UNIQUE(FILTER(Table2[Calculation],Table2[Calculation]<>""))

     

    Here is a excel with it in there. Hope its what you wanted and it helps.

     

    • MLT-CH's avatar
      MLT-CH
      Copper Contributor

      erictribble Thank you so much, I must admit it is a different language to me and I cannot seem to see the end values out of the Unique Totals. what is shown is just the categorie with the VAT % rather than the total sum of that specific category VAT amount. Any ideas?

      Thanks!

      • erictribble's avatar
        erictribble
        Copper Contributor

        MLT-CH Ok, so let me see if maybe I'm am getting what you need, correct first.

        So from your example table:

        Report IDMerchantCategoryTotalVATVAT paidDescriptionDate
        Expense Report #4 MaterialsCHF44.530% 0 31-12-22
        Expense Report #4 MaterialsCHF32.207.7% 2.3 30-12-22
        Expense Report #4 MaterialsCHF14.750% 0 30-12-22
        Expense Report #4 MaterialsCHF19.230% 0 30-12-22
        Expense Report #4 Representation expensesCHF8.902.5% 0.22 30-12-22
        Expense Report #4 MaterialsCHF82.157.7% 5.87 29-12-22


        And your explanation:

        "I need a formula that will calculate the sum of each VAT rate per category (e.g. Materials Q1/22 7.7%, Material Q1/22 2.5%, Materials Q1/22 0% etc. per category."

        --------------------------------------------------------------------------------------------------------


        Are you needing a total per quarter per category, end solution per the above example table:

        • Materials Q4/ 15.4%
          • [Line 2: Materials | 7.7% | 30-12-22] Plus [Line 6: Materials |  7.7% | 29-12-22] (since the other materials lines are at 0% VATS.

        OR Are you needing the result per line like:

        CategoryTotalVATVAT paidDateTotal
        MaterialsCHF44.530% 031-12-22Materials Q4/0%
        MaterialsCHF32.207.7% 2.330-12-22Materials Q4/ 7.7%
        MaterialsCHF14.750% 030-12-22Materials Q4/0%
        MaterialsCHF19.230% 030-12-22Materials Q4/0%
        Representation expensesCHF8.902.5% 0.2230-12-22Representation expenses Q4/2.5%
        MaterialsCHF82.157.7% 5.8729-12-22Materials Q4/7.7%

Resources