Discussion Re: SUM totals using VLOOKUP? in Excel
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2558087#M107024
<P>Try this:</P><P>=SUMIFS(Dates[Column2],Dates[Column1],CONCAT(">=",A1),Dates[Column1],CONCAT("<=",B1))</P><P> </P><P>Where your Dates worksheet table is named Dates;</P><P>Sheet Dates has dates in column A and values in column B</P><P>Sheet Summary has start date in column A and end date in column B.</P><P>I placed the formula in cell D1 on the Summary sheet. Copy the formula down to the other 2 lines.</P><P> </P><P><LI-USER uid="1105737"></LI-USER> </P>Sat, 17 Jul 2021 03:52:27 GMTCharlesRegan2021-07-17T03:52:27ZSUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2557976#M107017
<P>Hi All</P><P>I have a table with dates in col A and a value in Col B in Sheet:Dates</P><P>Lets say Col A is July 1st to July 31 and to make things easy the Value in Col B is the day</P><P>So in sequence it would be </P><P>01/07/21 1</P><P>02/07/21 2</P><P>:</P><P>:</P><P>30/07/21 30</P><P>31/07/21 31</P><P> </P><P>Now in Sheet:Summary in the same workbook I want to total the values between 2 dates.</P><P>These dates are already populated in column C and D</P><P> </P><P>Start date End Date Total</P><P>01/07/21 05/07/21 Formula? (total should be 15)</P><P>20/07/21 25/07/21 Formula? (total should be 135)</P><P>29/07/21 31/07/21 Formula? (total should be 90)</P><P> </P><P>Can someone Help me with this</P>Sat, 17 Jul 2021 00:31:53 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2557976#M107017MarcusBam2021-07-17T00:31:53ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2558050#M107023
<P><LI-USER uid="1105737"></LI-USER> </P><P><EM>You may try this one:</EM></P><P> </P><P><STRONG>:Caveat: </STRONG><EM>For better understanding & visualization I've taken smaller data set you need to extend as required.</EM></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="RajeshS_0-1626492584694.png" style="width: 551px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/296551iFF753B26C3CD056F/image-size/medium?v=v2&px=400" role="button" title="RajeshS_0-1626492584694.png" alt="RajeshS_0-1626492584694.png" /></span></P><P> </P><P> </P><P>Formula in cell U2:</P><P> </P><LI-CODE lang="applescript">=SUMPRODUCT(($W$2:$W$21>=$S2)*($W$2:$W$21<=$T2)*($X$2:$X$21))</LI-CODE><P> </P><P> </P><P><STRONG>N.B. </STRONG><EM>Adjust cell references in the formula as needed.</EM></P>Sat, 17 Jul 2021 03:30:03 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2558050#M107023Rajesh-S2021-07-17T03:30:03ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2558087#M107024
<P>Try this:</P><P>=SUMIFS(Dates[Column2],Dates[Column1],CONCAT(">=",A1),Dates[Column1],CONCAT("<=",B1))</P><P> </P><P>Where your Dates worksheet table is named Dates;</P><P>Sheet Dates has dates in column A and values in column B</P><P>Sheet Summary has start date in column A and end date in column B.</P><P>I placed the formula in cell D1 on the Summary sheet. Copy the formula down to the other 2 lines.</P><P> </P><P><LI-USER uid="1105737"></LI-USER> </P>Sat, 17 Jul 2021 03:52:27 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2558087#M107024CharlesRegan2021-07-17T03:52:27ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559220#M107103
<P><LI-USER uid="93699"></LI-USER> </P><P>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.</P>Sat, 17 Jul 2021 22:11:34 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559220#M107103MarcusBam2021-07-17T22:11:34ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559221#M107104
Hi<BR />Thank you for your solution. However I used the one <LI-USER uid="93699" login="Rajesh-S"></LI-USER> suggested and it works perfectly.<BR />Thank you for your time in providing a solution.Sat, 17 Jul 2021 22:16:03 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559221#M107104MarcusBam2021-07-17T22:16:03ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559554#M107126
<P><LI-USER uid="1105737"></LI-USER> </P><P> </P><P>Your issue was get the total between dates or range of dates,, so there is no use of any Lookup function.</P><P>I've used SUMPRODUCT since is faster & easier otherwise SUMIF of SUMIFS can be used also.</P><P> </P><P>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.</P>Sun, 18 Jul 2021 05:16:25 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559554#M107126Rajesh-S2021-07-18T05:16:25ZRe: SUM totals using VLOOKUP?
https://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559557#M107129
Glad to help you,, keep asking ☺<BR />Sun, 18 Jul 2021 05:17:57 GMThttps://techcommunity.microsoft.com/t5/excel/sum-totals-using-vlookup/m-p/2559557#M107129Rajesh-S2021-07-18T05:17:57Z