Forum Discussion
MLT-CH
Jun 30, 2023Copper 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 |
- erictribbleCopper 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-CHCopper 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!
- erictribbleCopper 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 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% - Materials Q4/ 15.4%