Formula Errors

Use SUMIF with INDEX and MATCH to Sum by Month in Excel

Elise WilliamsElise Williams Nov 22, 2026 868 views

Question details

The user needs a formula that sums values for an entity from a dynamically selected month column.

Use SUMIF with INDEX and MATCH to Sum by Month in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A budget worksheet has entity names in one column and monthly values across separate columns. A second sheet supplies the entity and month to sum.
Observed behavior
The formula returns incorrect results or fails when the selected month does not match a column header.
Before you start

Check that the Budget by Entity sheet has entity names in column A, month headers in B1:F1, and values below those headers. On the Flash sheet, verify that B4 contains the month and F15 contains the entity to match.

Solution 1Recommended

Select the month column with INDEX and MATCH

Use this formula to sum the selected month's values for the entity in Flash!F15.

MATCH finds the month header, and INDEX returns the corresponding column for SUMIF. The omitted row argument lets INDEX return the whole selected column within the specified range.

1
Check the month label

Compare Flash!B4 with the headers in 'Budget by Entity'!B1:F1. Use the same label, such as Mar rather than March, and remove any extra spaces.

2
Enter the formula

On the Flash sheet, select the result cell and enter =SUMIF('Budget by Entity'!$A$2:$A$100,Flash!$F15,INDEX('Budget by Entity'!$B$2:$F$100,,MATCH(Flash!$B$4,'Budget by Entity'!$B$1:$F$1,0))).

3
Adjust the data ranges

In the formula bar, change row 100 to the last row of your data. Keep the row boundaries of the SUMIF criteria range and INDEX data range aligned.

Select the month column with INDEX and MATCH
Keep ranges manageable: Bounded ranges reduce the amount of data processed compared with full-column references. Update the ranges when new rows are added, or use an Excel table for expanding data.
Calculate monthly totals in WPS

Sum a Selected Month in WPS Spreadsheets

Use SUMIF, INDEX and MATCH in WPS Spreadsheets to select a month column and sum its values for a chosen entity.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Check the inputs: On the Flash sheet, enter a month label in B4 that matches a header on Budget by Entity, and enter the entity name in F15.
  3. 3. Enter the formula: Select the result cell on Flash and enter =SUMIF('Budget by Entity'!$A$2:$A$100,Flash!$F15,INDEX('Budget by Entity'!$B$2:$F$100,,MATCH(Flash!$B$4,'Budget by Entity'!$B$1:$F$1,0))).
  4. 4. Review the ranges: Use the formula bar to adjust row 100 to include your data, keeping both data ranges aligned, then press Enter.
Calculate entity totals from a dynamically selected month columnOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with bounded ranges to limit unnecessary calculations
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #N/A?

MATCH returns #N/A when it cannot find the value in Flash!B4 among the headers in 'Budget by Entity'!B1:F1. Check for differences such as March versus Mar, extra spaces, or a text label being compared with a date value.

Why does the formula return zero for an entity?

Check that Flash!F15 matches the entity names in column A and that the selected month's cells contain numeric values. Also make sure the formula's ranges include the relevant rows.

What does the blank argument in INDEX do?

In INDEX(data_range,,column_number), the omitted row argument returns the selected column from the data range. SUMIF then uses that column as its sum range.

Can I use full-column references in this formula?

Yes, but references such as A:A and B:F can process more than a million rows and may slow the workbook. Use matching bounded ranges or an Excel table when practical.