SUM totals using VLOOKUP?

New Contributor

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

6 Replies
best response confirmed by MarcusBam (New Contributor)


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:





N.B. Adjust cell references in the formula as needed.

Try this:



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.




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.

Thank you for your solution. However I used the one @Rajesh-S suggested and it works perfectly.
Thank you for your time in providing a solution.



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.

Glad to help you,, keep asking ☺