SUMIFS Function to calculate quarterly different VAT rates

[MLT-CH]

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

[#2]

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

[#3]

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

[#4]

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

So from your example table:

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

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:

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%