Forum Discussion
SUM totals using VLOOKUP?
Hi All
I have a table with dates in col A and a value in Col B in Sheet:Dates
Lets say Col A is July 1st to July 31 and to make things easy the Value in Col B is the day
So in sequence it would be
01/07/21 1
02/07/21 2
:
:
30/07/21 30
31/07/21 31
Now in Sheet:Summary in the same workbook I want to total the values between 2 dates.
These dates are already populated in column C and D
Start date End Date Total
01/07/21 05/07/21 Formula? (total should be 15)
20/07/21 25/07/21 Formula? (total should be 135)
29/07/21 31/07/21 Formula? (total should be 90)
Can someone Help me with this
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.
6 Replies
Try this:
=SUMIFS(Dates[Column2],Dates[Column1],CONCAT(">=",A1),Dates[Column1],CONCAT("<=",B1))
Where your Dates worksheet table is named Dates;
Sheet Dates has dates in column A and values in column B
Sheet Summary has start date in column A and end date in column B.
I placed the formula in cell D1 on the Summary sheet. Copy the formula down to the other 2 lines.
- Hi
Thank you for your solution. However I used the one Rajesh_Sinha suggested and it works perfectly.
Thank you for your time in providing a solution.- Glad to help you,, keep asking ☺
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.
