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?
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.
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?
Try:
it sums all sheets where A1="X" and then divides by the count of sheets with A1="X"Please Login or Register to view this content.
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...
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.
thanks so much NBVC. you were extremely helpful and i appreciate it.
Last edited by kmfdm515; 01-22-2008 at 11:26 PM.
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
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]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks