SOLVED

SUM totals using VLOOKUP?

Copper 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 (Copper Contributor)
Solution

@MarcusBam 

You may try this one:

 

:Caveat: For better understanding & visualization I've taken smaller data set you need to extend as required.

RajeshS_0-1626492584694.png

 

 

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.

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.

 

@MarcusBam 

@Rajesh_Sinha 

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.

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.

@MarcusBam 

 

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 ☺
1 best response

Accepted Solutions
best response confirmed by MarcusBam (Copper Contributor)
Solution

@MarcusBam 

You may try this one:

 

:Caveat: For better understanding & visualization I've taken smaller data set you need to extend as required.

RajeshS_0-1626492584694.png

 

 

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.

View solution in original post