Results 1 to 4 of 4

Thread: Sum Vlookup

  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Sum Vlookup

    I'm trying to sum multiple sheets using the vlookup command. I can get my formula to sum everything between Sheet1 and Sheet2 with
    Code:
    =SUM(Sheet1:Sheet2!$J$40)
    and also get the vlookup to lookup what data i want with
    Code:
    =VLOOKUP(B3,'12-06-2015'!B6:J39,9,FALSE)
    Now i want to get a sum of the values in the lookup between sheet1 and sheet2. All of the sheets in this workbook are named for dates, 11-29-2015, 12-06-2015, etc. Everything i find through searching says to use SUMIF, I've never used SUMIF so i'm not sure how to proceed. B6 to J39 is where all the data is located. Any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Based on your posted formula, the only thing I can suggest is something like this
    Code:
    =SUMIF(Sheet1!B6:B39,B3,Sheet1!J6:J39)+SUMIF(Sheet2!B6:B39,B3,Sheet2!J6:J39)
    There is information on the SUMIF() and, if you are using Office 2007 or later, the SUMIFS() functions.

    HTH


    MTB

  3. #3
    Join Date
    May 2009
    Posts
    104
    Your formula works fine except i forgot to mention that i'm trying to SUM multiple sheets. The best way to do this that i have found is just to SUM everything between two blank sheets, Sheet1 and Sheet2. These two sheets are empty and just serve as markers to make the spreadsheet dynamic. All i have to do is copy a sheet, change the data, and the totals are automatically updated without changing any formulas. This has worked great in the past. What i have now is an excel workbook we use to sum up the weekly hours for all of our employees. There are sheets for each weekending date, 11-22-2015, 11-29-2015, and so on. Each sheet has a column for the Employees clock number Starting at B6 and ending at B39. There is also a column for the total hours for the week in column J, the data is at J6 through J39. I want to sum the hours in J6:J39 between sheet1 and sheet2 using the clock number as the criteria. Sorry for being vague before, sometimes i don't know how to ask the question until i have asked wrong the first time.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I have had a rummage round and I don't think it is possible to use a 'Cookie Cutter' formula with SUMIF() or SUMIFS().

    I do not know how many sheets you have, but if it is not too many and new inserted sheets are a copy of an existing sheet, then my suggestion is to add the SUMIF() formula to each sheet (a pain in the but I know, but only to do once?) and then add the Cookie Cutter formula(s) to the consolidation sheet.

    The criteria for the SUMIF() formula(s) could even be set on the consolidation sheet.

    Don't know if this a practical proposition, but the best I can come up with so fare.
    Of course you may have thought if this and rejected it.

    MTB

    ps this is all using Office 2007, thing may be different in later versions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •