SOLVED

SUM totals using VLOOKUP?

%3CLINGO-SUB%20id%3D%22lingo-sub-2557976%22%20slang%3D%22en-US%22%3ESUM%20totals%20using%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557976%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20dates%20in%20col%20A%20and%20a%20value%20in%20Col%20B%20in%20Sheet%3ADates%3C%2FP%3E%3CP%3ELets%20say%20Col%20A%20is%20July%201st%20to%20July%2031%20and%20to%20make%20things%20easy%20the%20Value%20in%20Col%20B%20is%20the%20day%3C%2FP%3E%3CP%3ESo%20in%20sequence%20it%20would%20be%26nbsp%3B%3C%2FP%3E%3CP%3E01%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3E02%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202%3C%2FP%3E%3CP%3E%3A%3C%2FP%3E%3CP%3E%3A%3C%2FP%3E%3CP%3E30%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2030%3C%2FP%3E%3CP%3E31%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2031%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20in%20Sheet%3ASummary%20in%20the%20same%20workbook%20I%20want%20to%20total%20the%20values%20between%202%20dates.%3C%2FP%3E%3CP%3EThese%20dates%20are%20already%20populated%20in%20column%20C%20and%20D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStart%20date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20Date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Total%3C%2FP%3E%3CP%3E01%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2005%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Formula%3F%20(total%20should%20be%2015)%3C%2FP%3E%3CP%3E20%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2025%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Formula%3F%20(total%20should%20be%20135)%3C%2FP%3E%3CP%3E29%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2031%2F07%2F21%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Formula%3F%20(total%20should%20be%2090)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20Help%20me%20with%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2557976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558050%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20totals%20using%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105737%22%20target%3D%22_blank%22%3E%40MarcusBam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20try%20this%20one%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3ACaveat%3A%26nbsp%3B%3C%2FSTRONG%3E%3CEM%3EFor%20better%20understanding%20%26amp%3B%20visualization%20I've%20taken%20smaller%20data%20set%20you%20need%20to%20extend%20as%20required.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RajeshS_0-1626492148812.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296550iA34F4967753071F2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RajeshS_0-1626492148812.png%22%20alt%3D%22RajeshS_0-1626492148812.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20in%20cell%20U2%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((%24W%242%3A%24W%2421%26gt%3B%3D%24S2)*(%24W%242%3A%24W%2421%26lt%3B%3D%24T2)*(%24X%242%3A%24X%2421))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3CEM%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558087%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20totals%20using%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558087%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CP%3E%3DSUMIFS(Dates%5BColumn2%5D%2CDates%5BColumn1%5D%2CCONCAT(%22%26gt%3B%3D%22%2CA1)%2CDates%5BColumn1%5D%2CCONCAT(%22%26lt%3B%3D%22%2CB1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20your%20Dates%20worksheet%20table%20is%20named%20Dates%3B%3C%2FP%3E%3CP%3ESheet%20Dates%20has%20dates%20in%20column%20A%20and%20values%20in%20column%20B%3C%2FP%3E%3CP%3ESheet%20Summary%20has%20start%20date%20in%20column%20A%20and%20end%20date%20in%20column%20B.%3C%2FP%3E%3CP%3EI%20placed%20the%20formula%20in%20cell%20D1%20on%20the%20Summary%20sheet.%26nbsp%3B%20Copy%20the%20formula%20down%20to%20the%20other%202%20lines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105737%22%20target%3D%22_blank%22%3E%40MarcusBam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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)
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-S 

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-S 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 ☺