Function Problems

Excel SUMIFS Formula to Match Multiple Year and Month Columns

Partner Editor Sep 25, 2026 869 views

Question details

The user needs a formula to calculate total expenses based on multiple matching criteria, specifically filtering by category, year, and month.

Excel Formula to Match Multiple Year and Month Columns

Product: Excel
Device & OS: not provided
Scenario: Calculating aggregated expense totals in a financial tracker where the target data must simultaneously match specific categories and time periods (year and month).
Observed behavior: The user requires an accurate functional formula that evaluates multiple distinct criteria columns before returning a summed total for the matched rows.

Before you start

Ensure your expense data is properly formatted as an Excel Table or named range, and clearly identify the cells containing your target year and month criteria.

Solution 1Recommended

Use the SUMIFS Function for Multiple Criteria

The SUMIFS function is the standard and most efficient method for summing values that meet two or more specific criteria in Excel.

SUMIFS evaluates multiple conditions simultaneously. It requires a sum range, followed by pairs of criteria ranges and criteria. This is perfect for checking category, year, and month columns against target values.

Define the Sum Range

Begin your formula with =SUMIFS( and select the column containing the amounts you want to calculate, such as Expense[Total Amount].

Add Category Criteria

Select the criteria range for the category, followed by the target criteria cell. For example: Expense[Expense Category], $R3.

Add Year and Month Criteria

Continue adding pairs for the year and month. Reference your specific setup sheet cells for the criteria, such as Expense[Year], 'Set Up'!$C$30, Expense[Month], 'Set Up'!$C$31.

Complete the Formula

Close the parenthesis to finish the formula. The final formula will look similar to =SUMIFS(Expense[Total Amount], Expense[Expense Category], $R3, Expense[Year], 'Set Up'!$C$30, Expense[Month], 'Set Up'!$C$31). Press Enter to calculate.

Use the SUMIFS Function for Multiple Criteria

Formula Tip: Using structured references (like Expense[Year]) instead of standard cell references (like A2:A100) makes your formula much easier to read and automatically updates as you add new rows to your data.

Solution 2

Combine SUM and SUMIFS for Array Targets

If you need to match expenses against multiple months or years simultaneously, you can wrap your SUMIFS formula inside a SUM function.

Advanced Spreadsheet Calculation

Calculate Complex Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS, making it incredibly simple to calculate detailed expense totals across multiple years, months, and categories.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your expense records.
2. Insert Function: Click on the cell where you want the total to appear. Navigate to the 'Formulas' tab and click 'Insert Function'.
3. Search for SUMIFS: Search for 'SUMIFS' in the dialog box and click 'OK'.
4. Input Criteria Ranges: Use the visual prompt box to select your Sum_range, Criteria_range1, and Criteria1. Click the '+' or 'Add' button to include extra ranges for Year and Month.
5. Apply Formula: Click 'OK'. WPS Spreadsheet will instantly calculate and display the matched total.

100% compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in Formula Builder to help you easily manage complex SUMIFS criteria without syntax errors.Lightweight, fast, and completely free to download and use for your daily financial tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning a #VALUE! error?

A #VALUE! error in SUMIFS typically occurs because your sum_range and criteria_ranges are not the exact same size. Verify that all referenced ranges have identical starting and ending rows (e.g., if sum_range is A2:A100, your criteria_range must also be B2:B100).

Can I use SUMIFS to match a date range instead of separate year and month columns?

Yes. Instead of extracting the year and month into separate columns, you can use comparison operators on a full Date column. For example, use ">="&DATE(2023,1,1) as criteria 1 and "<="&DATE(2023,12,31) as criteria 2 to match everything within the year 2023.

Is the SUMIFS function case-sensitive when matching text categories?

No, SUMIFS is not case-sensitive. It treats text like 'EXPENSE', 'Expense', and 'expense' as the exact same matching criteria.

Excel & Data Problems