How to Sum Values Between Two Matching Week Numbers in Excel
Question details
The user needs to calculate the sum of specific columns of weekly costs that fall between a dynamically selected start week and end week.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking and reporting financial data where costs are organized into weekly columns, requiring a dynamic total based on flexible timeframe inputs.
- Observed behavior
- Requires an advanced formula to identify the column boundaries dynamically based on week numbers and calculate the sum of the interval without manually adjusting the range.
Ensure that the week numbers in your header row are unique and formatted exactly the same way (e.g., as text or numbers) as the start and end week criteria you plan to input.
Use SUM, INDEX, and MATCH to Define a Dynamic Range
This is the most robust and flexible method for dynamically summing values across columns between two specific markers.
By combining the SUM and INDEX functions with MATCH, you can create a dynamic range reference. The MATCH function locates the column position for your start and end weeks. Then, INDEX converts those positions into actual cell references that the SUM function uses to calculate the total.
Designate two cells outside your data table to serve as input fields for your desired 'Start Week' and 'End Week' (for example, use cells A5 and B5).
Determine the row containing your week headers (e.g., B1:ZZ1) and the corresponding row containing the departmental data you want to sum (e.g., B2:ZZ2).
Click on the cell where you want the total to appear and enter the formula: =SUM(INDEX(B2:ZZ2,1,MATCH(A5,B1:ZZ1,0)):INDEX(B2:ZZ2,1,MATCH(B5,B1:ZZ1,0)))
Press Enter to calculate the sum. Whenever you update the start and end week values in your criteria cells, the summed total will automatically adjust.
Alternative: Use SUMIFS for Numerical or Date-based Headers
If your weekly columns are represented by actual dates or strict numerical values instead of text labels, SUMIFS can efficiently sum the interval.
Easily Sum Dynamic Ranges with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas including SUM, INDEX, and MATCH, allowing you to manage your weekly cost reports effortlessly. Enjoy a familiar interface and comprehensive function library without any hassle.
- 1. Open your file: Launch WPS Office and open your spreadsheet workbook containing the weekly cost data.
- 2. Select the target cell: Click on the cell where you want the dynamic sum to be displayed for your department.
- 3. Apply the formula: Enter the formula =SUM(INDEX(range,1,MATCH(start)):INDEX(range,1,MATCH(end))) and press Enter to calculate instantly.
Frequently Asked Questions
Why is my SUM and INDEX formula returning a #N/A error?
This usually happens if the MATCH function cannot find the exact start or end week number in your header row. Ensure that there are no extra spaces, spelling mistakes, or hidden formatting differences between your criteria input cell and the header row.
Can I copy and drag this formula down for multiple departments?
Yes. To do this properly, ensure your header ranges in the MATCH function are set as absolute references (e.g., $B$1:$ZZ$1), while leaving the row numbers in the INDEX data range relative (e.g., B2:ZZ2) so they update as you drag the formula down.
Can I use XLOOKUP instead of INDEX and MATCH for this task?
Absolutely. In modern spreadsheet software, you can use the syntax =SUM(XLOOKUP(StartWeek, B1:ZZ1, B2:ZZ2):XLOOKUP(EndWeek, B1:ZZ1, B2:ZZ2)) to achieve the exact same dynamic range summation with a slightly shorter formula.
