Forum Discussion
SUM totals using VLOOKUP?
You may try this one:
:Caveat: For better understanding & visualization I've taken smaller data set you need to extend as required.
Formula in cell U2:
=SUMPRODUCT(($W$2:$W$21>=$S2)*($W$2:$W$21<=$T2)*($X$2:$X$21))N.B. Adjust cell references in the formula as needed.
You may try this one:
:Caveat: For better understanding & visualization I've taken smaller data set you need to extend as required.
Formula in cell U2:
=SUMPRODUCT(($W$2:$W$21>=$S2)*($W$2:$W$21<=$T2)*($X$2:$X$21))
N.B. Adjust cell references in the formula as needed.
Thank you for the solution - I do not quite understand how it works but I have translated your solution to my data and it works perfectly.
Your issue was get the total between dates or range of dates,, so there is no use of any Lookup function.
I've used SUMPRODUCT since is faster & easier otherwise SUMIF of SUMIFS can be used also.
The formula first picks date from S2 and is 01/07/2021 and used operator is >=,, means check date/dates which is/are either Greater than or Equals to 01/07/2021 in column W, and then picks date from T2, is 05/07/2021 and the operator is <=,,, is Less than or Equals to,, and in this way it's like any date/dates between 01/07/2021 and 05/07/2021,,, and SUMS all related values from column X. Then moves for next combination.
