Forum Discussion

Matthew Merrick's avatar
Matthew Merrick
Copper Contributor
Jan 30, 2018

SUM function with vlookup referencing the start and stop cells of the range.

I am attempting to write a formula for adding up all the cells within a range using vlookup as the start and stop points of the sum function.  The real life description is to add up the containers received between two dates.  The attached file has what I am working with.  The cell I am working on is cell E14 on the "test" tab.  What I want to happen is to add up all the containers received between the two dates at the top of the page (cells E4 and K4).  This information entered on the "Cont Rcvd" tab in the cell range of B3:K33.  In this case the column index number is 2.  Here is the equation I used:

=SUM((VLOOKUP($E$4,'Cont Rcvd'!$A$3:$K$33,2,FALSE)+(VLOOKUP($K$4,'Cont Rcvd'!$A$3:$K$33,2,FALSE))).  I keep getting the "if you want to show text....." error.

Thanks in advance for any help you can offer.

Formulas & Functions
Need Help
  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Iron Contributor
    Feb 01, 2018

    VLOOKUP returns a value from a specified number of columns to the right of the found value in the first column of the lookup range.  After correcting your formula which was missing a closing bracket, it returns 0 because that's what each of the VLOOKUPs return.

     

    To SUM a range between two dates specified in another column you need one of the conditional sum formulas - SUMIF or SUMIFS.  In your case, SUMIFS is needed because you have more than one condition.

    =SUMIFS('Cont Rcvd'!B3:B33,'Cont Rcvd'!$A$3:$A$33,">="&Test!$E$4,'Cont Rcvd'!$A$3:$A$33,"<="&Test!$K$4)

    The relative referencing of 'Cont Rcvd'!B3:B33 combined with absolute referencing of the Dates in 'Cont Rcvd'!$A$3:$A$33 means the formula can be dragged/filled across row 14 to get numbers for the other items.

Resources