# selecting cells in multiple worksheets by criteria in formulas

1. ## 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. Try:

``Please Login or Register  to view this content.``

3. 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. 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. 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. 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. thanks so much NBVC. you were extremely helpful and i appreciate it.

8. ## 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. ## Re: selecting cells in multiple worksheets by criteria in formulas

shanestuart,

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

#### 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