Forum Discussion
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!
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% |