Use SUMIF with INDEX and MATCH to Sum by Month in Excel
Question details
The user needs a formula that sums values for an entity from a dynamically selected month column.

- 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.
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.
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.
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.
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))).
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.

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. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
- 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. 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. Review the ranges: Use the formula bar to adjust row 100 to include your data, keeping both data ranges aligned, then press Enter.

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.




