Jul 16 2021 05:31 PM
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
Jul 16 2021 08:25 PM - edited Jul 16 2021 08:30 PM
SolutionYou 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.
Jul 16 2021 08:47 PM - edited Jul 16 2021 08:52 PM
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.
Jul 17 2021 03:11 PM
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.
Jul 17 2021 03:16 PM
Jul 17 2021 10:13 PM - edited Jul 17 2021 10:16 PM
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.
Jul 16 2021 08:25 PM - edited Jul 16 2021 08:30 PM
SolutionYou 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.