Forum Discussion

Tolley123's avatar
Tolley123
Copper Contributor
May 20, 2023

INDEX & MATCH with SUMIF(S)

Hi assistance requested please.

I use INDEX/MATCH formula in report charts to pull Sales data for each commodity to provide weekly or monthly summaries. e.g. =INDEX(Sales!C$4:C$55,MATCH(C$6,Sales!B$4:B$55,0)).

Going forward I need to introduce weekly summaries on the progress through each month i.e. for each commodity: (i) after week two of the month show the sum for week one and week two; (ii) after week three of the month show the sum for weeks one, two and three; (iii) etc.

In the above formula:

  1. C6 is a drop-down list in the chart that enables a specific week to be selected;
  2. Column B is a date column (end of each week); and,
  3. Column C is the total sales for a given commodity; other columns provide similar data for other commodities.

Help with what the revised formula should look like would be appreciated. Thanks in advance.

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 20, 2023

    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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 20, 2023

    Tolley123 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources