SOLVED

New Contributor

# 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

6 Replies
best response confirmed by MarcusBam (New Contributor)
Solution

# Re: 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.

# Re: SUM totals using VLOOKUP?

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.

# Re: SUM totals using VLOOKUP?

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.

# Re: SUM totals using VLOOKUP?

Hi
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.

# Re: SUM totals using VLOOKUP?

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.