Jun 30 2023 07:22 AM
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 |
Jun 30 2023 09:51 AM
@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.
Jul 03 2023 02:22 AM
@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!
Jul 03 2023 07:36 AM - edited Jul 03 2023 07:37 AM
@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:
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% |