Use Excel SUMIFS with a Drop-Down Year to Sum Table Revenue
Question details
Create a SUMIFS formula that uses a year selected from a drop-down list to sum revenue from the matching yearly table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A report contains a year selector in cell C14 and needs to summarize revenue by the division listed in cell B15.
- Observed behavior
- The formula should use datatable_24 when 2024 is selected and datatable_23 when 2023 is selected, then return the revenue total for the selected division.
Confirm that the drop-down values match the expected year text, such as 2023 and 2024, and that both Excel tables have Revenue and Division column headers. Verify the table names in the Table Design tab before entering the formula.
Use IF to Select the Correct Yearly Table in SUMIFS
Use this direct approach when the report needs to switch between the 2023 and 2024 revenue tables.
The IF function checks the year selected in C14. It then runs SUMIFS against the corresponding structured table and uses the division in B15 as the criterion.
Click the cell where the division revenue total should appear, such as C15 on the report sheet.
Type =IF(C$14="2024",SUMIFS(datatable_24[Revenue],datatable_24[Division],$B15),SUMIFS(datatable_23[Revenue],datatable_23[Division],$B15)) and press Enter.
Use the drop-down in C14 to select 2024 or 2023. The formula uses datatable_24 for 2024 and datatable_23 for the other option.
Drag the fill handle down from the formula cell to calculate totals for additional division names listed in column B.
Use SWITCH When the Report Has Several Year Tables
Use this method when you have more than two yearly tables and want to explicitly map each selected year to a result.
Build a Drop-Down Year SUMIFS Report in WPS Spreadsheet
WPS Spreadsheet lets you create a year selector, organize yearly data in tables, and use IF or SWITCH with SUMIFS to calculate division revenue from the selected dataset.
- 1. Open the workbook in WPS Spreadsheet: Open the Excel workbook in WPS Office and select the sheet containing the year selector and revenue report.
- 2. Create or review the year drop-down: Select cell C14, then use Data > Data Validation to create a List containing 2023 and 2024 if the selector is not already configured.
- 3. Verify table and column names: Click within each source table and confirm the table names and Revenue and Division headers before referencing them in a formula.
- 4. Enter the dynamic SUMIFS formula: Select the report total cell and enter =IF(C$14="2024",SUMIFS(datatable_24[Revenue],datatable_24[Division],$B15),SUMIFS(datatable_23[Revenue],datatable_23[Division],$B15)).
- 5. Change the year to test the report: Choose each year from the C14 drop-down and confirm that the division totals update from the corresponding table.
Frequently Asked Questions
Why does my SUMIFS formula not change when I select a different year?
Check that C14 contains the exact value tested by the formula. For example, if the formula tests "2024", the drop-down must return 2024 as matching text, and the workbook calculation mode should be Automatic.
Can I use a numeric year instead of text in the IF formula?
Yes. If C14 stores a true number rather than text, remove the quotation marks around the year: =IF(C$14=2024,SUMIFS(...),SUMIFS(...)).
How do I find an Excel table name used in a structured reference?
Click any cell in the table, open the Table Design tab, and review the Table Name box. Use that exact name in references such as datatable_24[Revenue].
Can I add a third year to this type of revenue report?
Yes. Use SWITCH with a SUMIFS calculation for each year, or add another nested IF condition. SWITCH is generally clearer when the report includes three or more yearly tables.
What happens if the division in B15 is not found in the selected table?
SUMIFS returns 0 when no rows in the selected table have a Division value that matches B15.
