arraysexcelindexingmatchaggregate

Aggregate/Match Conditions Based on Date Range

Need to retrieve matching Date, Type, ID, and reference Cost based on user input Date Range. In Data Set 1 and Data Set 2 inputs are not sorted, and the match conditions need to look at if it falls within the Date Range first, check if the Type matches second, and if the IDs match third. If all (3) of these conditions pass it will list them in the Desired Output and pull the associated Cost. If the Cost occur on the same date, it will add them together. Prefer an Excel 2019 formula-based approach over VBA.

enter image description here

Data Set 1 Data Set 2 Date Range Example of Desired Output
Date Type ID Cost Date Type ID Cost 1/1/25 2/28/25 Date Type ID Cost
2/18/25 Apple 123 $5 2/18/25 Apple 456 $30 User Input 1/16/25 Banana 104 $75
2/21/25 Pear 102 $10 1/29/25 Pear 789 $35 1/28/25 Pear 789 $15
1/28/25 Pear 789 $15 1/28/25 Mango 852 $40 1/29/25 Pear 789 $35
2/8/25 Apple 879 $20 2/8/25 Apple 123 $45 2/8/25 Apple 123 $45
1/16/25 Banana 104 $25 1/16/25 Banana 104 $50 2/18/25 Apple 123 $5
7/5/25 Berry 222 $45 10/8/25 Berry 145 $8
8/5/25 Melon 456 $13 5/4/25 Melon 654 $45
3/8/25 Kiwi 852 $18 6/9/25 Kiwi 321 $15
Solution

  • If your output section has for example:

    Time Range Start: M3

    Time Range End: N3

    Type: O4

    ID: P4

    Use this formula to sum the costs from both sheets based on conditions:

    =SUMIFS(F:F, A:A, ">="&$M$3, A:A, "<="&$N$3, D:D, O4, E:E, P4) +

    SUMIFS(K:K, H:H, ">="&$M$3, H:H, "<="&$N$3, I:I, O4, J:J, P4)

    The first SUMIFS checks Sheet1 (A:A for Date, D:D for Type, E:E for ID, F:F for Cost).

    The second SUMIFS checks Sheet2 (H:H for Date, I:I for Type, J:J for ID, K:K for Cost).

    Assuming the first output row for Date is in cell Q4, use:

    =IFERROR(INDEX(A:A, MATCH(1, (A:A >= $M$3) * (A:A <= $N$3) * (D:D = O4) * (E:E = P4), 0)), "")