# SUMIFS Function to calculate quarterly different VAT rates

Copper Contributor

# 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 ID Merchant Category Total VAT VAT paid Description Date Expense Report #4 Materials CHF44.53 0% 0 31-12-22 Expense Report #4 Materials CHF32.20 7.7% 2.3 30-12-22 Expense Report #4 Materials CHF14.75 0% 0 30-12-22 Expense Report #4 Materials CHF19.23 0% 0 30-12-22 Expense Report #4 Representation expenses CHF8.90 2.5% 0.22 30-12-22 Expense Report #4 Materials CHF82.15 7.7% 5.87 29-12-22
3 Replies

# Re: SUMIFS Function to calculate quarterly different VAT rates

@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.

# Re: SUMIFS Function to calculate quarterly different VAT rates

@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!

# Re: SUMIFS Function to calculate quarterly different VAT rates

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

 Report ID Merchant Category Total VAT VAT paid Description Date Expense Report #4 Materials CHF44.53 0% 0 31-12-22 Expense Report #4 Materials CHF32.20 7.7% 2.3 30-12-22 Expense Report #4 Materials CHF14.75 0% 0 30-12-22 Expense Report #4 Materials CHF19.23 0% 0 30-12-22 Expense Report #4 Representation expenses CHF8.90 2.5% 0.22 30-12-22 Expense Report #4 Materials CHF82.15 7.7% 5.87 29-12-22

"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:

 Category Total VAT VAT paid Date Total Materials CHF44.53 0% 0 31-12-22 Materials Q4/0% Materials CHF32.20 7.7% 2.3 30-12-22 Materials Q4/ 7.7% Materials CHF14.75 0% 0 30-12-22 Materials Q4/0% Materials CHF19.23 0% 0 30-12-22 Materials Q4/0% Representation expenses CHF8.90 2.5% 0.22 30-12-22 Representation expenses Q4/2.5% Materials CHF82.15 7.7% 5.87 29-12-22 Materials Q4/7.7%