Use SUMIFS in WPS Spreadsheets to Calculate VAT Totals by Category, Quarter, and Rate
Question details
The user needs to build a VAT report that totals amounts by merchant category, quarter, year, and VAT rate using SUMIFS.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source data contains merchant categories, transaction amounts, VAT percentages, and dates; the goal is to summarize VAT totals for each category, quarter, year, and rate.
- Observed behavior
- The report currently lacks grouped VAT totals that update from the source table.
Prepare a source table with columns for Category, Total, VAT rate, and Date. Ensure dates are stored in a consistent date format and VAT rates are entered as percentages or decimal values.
Calculate VAT totals with SUMIFS helper columns
Use this formula-driven method when you need grouped VAT totals that automatically update as the source data changes.
This method works well in WPS Spreadsheets because SUMIFS supports multiple criteria and structured table references. A helper column converts each date into a quarter-year label so the SUMIFS formula can group records correctly.
In the first blank column next to the source table, enter ="Q"&INT((MONTH([@Date])+2)/3)&"-"&YEAR([@Date]) and press Enter. Fill the formula down through all data rows.
Click the first summary cell where the VAT total should appear, type =SUMIFS(Table1[Total],Table1[Category],A2,Table1[QuarterYear],B2,Table1[VAT],C2), and press Enter. Replace table and cell references to match your workbook.
Select the cells that contain VAT rates or percentage-based results, press Ctrl+1, choose Percentage, and set the desired number of decimal places.
Use =UNIQUE(Table1[Category]), =UNIQUE(Table1[QuarterYear]), and =UNIQUE(Table1[VAT]) for each list, or go to Insert > PivotTable to build the full category, quarter, and rate combination report.
Create a quarter-based VAT summary with a PivotTable
Use this visual alternative when you prefer an interactive report without manually writing SUMIFS formulas.
Build a VAT Category, Quarter, and Rate Report with SUMIFS in WPS Spreadsheets
WPS Spreadsheets can complete this task directly using SUMIFS with helper columns or a PivotTable. Create a quarter-year helper column, apply SUMIFS with criteria for category, period, and VAT rate, and format the results as percentages for a clear VAT summary.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the .xlsx file that contains the VAT source data.
- 2. Add the QuarterYear helper column: In the first empty column next to the source table, enter ="Q"&INT((MONTH([@Date])+2)/3)&"-"&YEAR([@Date]) and copy it down through all rows.
- 3. Create the SUMIFS formula: Click the first summary cell, type =SUMIFS(Table1[Total],Table1[Category],A2,Table1[QuarterYear],B2,Table1[VAT],C2), and press Enter. Adjust table and cell references as needed.
- 4. Review totals and format percentages: Select the VAT rate or VAT total cells, press Ctrl+1, choose Percentage or Currency, and save the workbook.
Frequently Asked Questions
Can WPS Spreadsheets use the same SUMIFS syntax as Excel?
Yes. WPS Spreadsheets supports SUMIFS with the same arguments as Excel. Open your workbook, click the formula bar, and enter the SUMIFS formula using your table and column references.
How do I create a quarter-year helper column from dates?
Insert a new column next to the date column and enter ="Q"&INT((MONTH([@Date])+2)/3)&"-"&YEAR([@Date]). Copy the formula down; if dates are stored as text, convert them to real dates first.
How do I list unique category, quarter, and VAT combinations?
Use =UNIQUE(Table1[Category]), =UNIQUE(Table1[QuarterYear]), and =UNIQUE(Table1[VAT]) in separate areas, or insert a PivotTable from the Insert tab and drag Category, QuarterYear, and VAT to Rows.
Can I format VAT rates as percentages in the formula result?
Yes. Select the VAT rate cells, press Ctrl+1, choose Percentage, and set decimal places. To display a formula result as a text percentage, wrap it with TEXT(value,"0.00%").
What if my source table name or column names are different?
Replace Table1 and the column names in the SUMIFS formula with your actual table name and header names. You can check or edit table names under the Table Tools context tab.
