+ Reply to Thread
Results 1 to 9 of 9

selecting cells in multiple worksheets by criteria in formulas

  1. #1
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    selecting cells in multiple worksheets by criteria in formulas

    hi,

    here is what i am trying to do. i have a formulas that either SUM or AVERAGE cells across 70 workheets. now i need to make the same calculations, but only for sheets that have a specific value in one cell.

    ex: sum of cell B1 only in worksheets where A1="x".

    i have tried these SUMIFs:

    =SUMIF('sheet1:sheet70'!A1, x, 'sheet1:sheet70'!B1)

    =SUMIF('sheet1:sheet70'!A1, x, ['sheet1:sheet70'!B1])


    but apparently SUMIF does not work across multiple worksheets ranges?

    is there any way to do this?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108
    wow, you are really helpful! thanks!

    so this begs the same questions as my last topic...what about averages? i could use the same solution as last time where i divide by the number of sheets that match that criteria...but is there a way to do it so i wouldn't have to change the formula every time a new sheet is added?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    Please Login or Register  to view this content.
    it sums all sheets where A1="X" and then divides by the count of sheets with A1="X"

  5. #5
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108
    awesome, thanks!

    hate to keep asking questions, but how would this work if the sheets did not have numbers in them? if they were all just random names...

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    You would then have to create a list of all the sheetnames and you would name that list through Insert|Name|Define.. you would call that list something like "TabNames"...

    then the above formula would be revised to:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108
    thanks so much NBVC. you were extremely helpful and i appreciate it.
    Last edited by kmfdm515; 01-22-2008 at 11:26 PM.

  8. #8
    Registered User
    Join Date
    10-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: selecting cells in multiple worksheets by criteria in formulas

    How would you copy this formulat to another cell.....for example if you change from B1 to B2 and copy over the B1 follows instead of automatically changing to B2

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: selecting cells in multiple worksheets by criteria in formulas

    shanestuart,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1