INDEX & MATCH with SUMIF(S)

Tolley123 

First of all, as recommended by Mr. Hans Vogelaar , insert a file (without sensitive data) or photo/s. Additional information such as Excel version, operating system, storage medium, etc.) would be advantageous to get a more precise solution proposal.

Nevertheless, here is an example, with AI helps, of how you could possibly achieve your plans.

Assuming you have the following setup:

• Column B: Date column (end of each week)
• Column C: Total sales for a given commodity (for each week)
• Cell C6: Dropdown list to select the specific week

To calculate the weekly summaries for each commodity, you can use the following formula:

=SUMIFS(Sales!C$4:C$55, Sales!B$4:B$55, "<="&MAX($B$4:B6), Sales!B$4:B$55, ">="&MAX($B$4:B6)-WEEKDAY(MAX($B$4:B6)))

Here's how the formula works:

1. MAX($B$4:B6): This part of the formula calculates the maximum date value up to the current row in your summary chart. It determines the end of the latest week included in the summary.
2. MAX($B$4:B6)-WEEKDAY(MAX($B$4:B6)): This calculates the start of the latest week included in the summary by subtracting the weekday number (0 for Sunday, 1 for Monday, etc.) from the maximum date value.
3. Sales!C$4:C$55: This is the range of total sales for the given commodity in the Sales worksheet. Modify it based on your actual data range.
4. Sales!B$4:B$55: This is the range of dates in the Sales worksheet. Modify it based on your actual data range.

The formula uses the SUMIFS function to sum the sales values based on two criteria:

• Sales date is less than or equal to the maximum date of the current row (end of the latest week included).
• Sales date is greater than or equal to the start of the latest week included.

By using this formula in your chart, it will calculate the weekly summaries dynamically based on the selected week from the dropdown list (cell C6).

Note: Adjust the range references (Sales!C$4:C$55, Sales!B$4:B$55) and modify them according to the actual range of your data.