+ Reply to Thread
Results 1 to 14 of 14

Summing across many worksheets

  1. 09-12-2013, 01:09 PM #1
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Summing across many worksheets

    The objective is to search the same column ($T6:$T1000) through 39 of 43 worksheets and, whenever there’s a match, sum the #values of another column ($M6:$M1000) that’s to the left of the reference column ($T6:$T1000). The #value cell may be blank at times.

    I'm having some difficulty in nesting one equation with the functions VLOOKUP, INDIRECT, INDEX, MATCH, COUNTIF and CHOOSE.

    The NAME of the set of worksheets is “BldgTabs”

    Once I have the equation, it needs to be copied so the lookup-value (e.g: C11) of VLOOKUP will be relative and all other cell references in the equation are absolute.

    The equation has so far become;
    =VLOOKUP(C11,INDIRECT("'"&INDEX(BldgTabs,MATCH(TRUE,COUNTIF(INDIRECT("'"&BldgTabs&"'!$T$6:$T$1000"),$M$6:$M$113)>0,0))&"'!$M$6:$M$1000"),2,0)

    However the array referenced in VLOOKUP requires a CHOOSE function to get the lookup column into an array where it is to the left of the column having the #values that are to be added for any matched values.
    Reply With QuoteRegister To Reply
  2. 09-12-2013, 01:15 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Summing across many worksheets

    Hi and welcome to the forum

    To try and do this with 1 formula, is going to be a headache, I think. I would suggest 1 of 2 alternatives...

    1. get someone to put some VBA together for you (mine sucks)
    2. apply the vlookup (or whatever search you end up using) in/on EACH sheet, based on the criteria on the Master? sheet, and then use a simple =sum(sheet1:sheet43:A1) to add the values

    Perhaps a sample workbook would help to show what you are working with?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 09-12-2013, 02:16 PM #3
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    RE: option-1-heavy sigh, I'm not so well versed in VBA.
    RE: option-2-seems impractical as the summary list of compared items has a potential of over 1000 lines. So it needs to be kept to its own tab.
    Reply With QuoteRegister To Reply
  4. 09-12-2013, 02:25 PM #4
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    FYI: My excel file is 1.8MB and exceeds this site's limits
    Reply With QuoteRegister To Reply
  5. 09-12-2013, 06:11 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Summing across many worksheets

    Maybe delete a few sheets and/or remove info/data that is not relevant to your question?
    Reply With QuoteRegister To Reply
  6. 09-12-2013, 06:44 PM #6
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    Wondering if a simpler equation would work;

    =SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,COUNTIF(INDIRECT("'"&BldgTabs&"'!M6:M1000"), INDIRECT("'"&BldgTabs&"'!M6:M1000")>0)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 09-12-2013, 06:46 PM #7
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    In the excel file provided above, refer to the tab "Campus Summary-Fixt Type", cell; B11.
    Reply With QuoteRegister To Reply
  8. 09-12-2013, 07:52 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Summing across many worksheets

    See if the attached is something you can work with? I know its a bit long-winded, but I created a summary on your Index sheet that pulls in the totals for you...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-12-2013, 08:11 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summing across many worksheets

    Quote Originally Posted by John Blissett View Post
    Wondering if a simpler equation would work;

    =SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,COUNTIF(INDIRECT("'"&BldgTabs&"'!M6:M1000"), INDIRECT("'"&BldgTabs&"'!M6:M1000")>0)
    I don't understand your formula.

    The syntax for the SUMIF function is:

    SUMIF(criteria_range,criteria,sum_range)

    Your criteria_range is: INDIRECT("'"&BldgTabs&"'!T6:T1000")

    The criteria is: C794

    So, what's your sum_range? Why do you have a COUNTIF function where the sum_range should be?

    I didn't look at your file. I have a 50kb file download limit.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  10. 09-12-2013, 09:08 PM #10
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    So, what's your sum_range? Why do you have a COUNTIF function where the sum_range should be?

    ANSWER: the sum range is M6:M1000 (for qualified matches of T6:T1000)
    Reply With QuoteRegister To Reply
  11. 09-12-2013, 09:19 PM #11
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summing across many worksheets

    A typical formula to do what you want would look something like this...

    =SUMPRODUCT(SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,INDIRECT("'"&BldgTabs&"'!M6:M1000")))
    Reply With QuoteRegister To Reply
  12. 09-12-2013, 09:26 PM #12
    John Blissett
    John Blissett is offline
    Registered User
    Join Date
    09-12-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Summing across many worksheets

    That did it. Whoooo Hoooo! Thanks so much!!!
    Reply With QuoteRegister To Reply
  13. 09-12-2013, 09:28 PM #13
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summing across many worksheets

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
  14. 09-13-2013, 02:12 AM #14
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Summing across many worksheets

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Summing across worksheets
    By JJanssen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2008, 06:04 PM
  2. Summing up saved worksheets
    By shawnvw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2008, 12:48 PM
  3. Summing up data from various worksheets
    By mark_vi_ in forum Excel General
    Replies: 12
    Last Post: 08-21-2006, 01:17 PM
  4. [SOLVED] Summing across several worksheets
    By Al in forum Excel General
    Replies: 5
    Last Post: 03-16-2006, 09:10 AM
  5. Summing Number of Worksheets
    By PipHarrison in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2005, 09:54 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1