summing a value across multiple sheets
I have 52 sheets with a value against a name. For each name I need to add all the values in each sheet.
The names are in column A and the values to sum are in column T. The name criteria is in a summary sheet.
e.g
sheet 1
A T
smith £300
taylor £200
jones £100
sheet 2
A T
Smith £0
taylor £40
jones £1000
sheet 3
A T
Smith £242
jones £33
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect - 2111 is a release number, not the actual version you have. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
In Sheet4
Column A (from row 2)
list of Names
in B2
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A2:A100"),A2,INDIRECT("'"&Sheets&"'!T2:T100")))
Add Named Range "Sheets" for tab names
Last edited by JohnTopley; 12-19-2021 at 12:42 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
JohnOriginally Posted by JohnTopley
My sheets aren’t generically named but on a varying date.
Can I still use xx:yy after selecting all tabs?
What, exactly, do you mean by:
My sheets aren’t generically named but on a varying date
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If you are doing the summation over a range of sheets, then just include the Sheet names in the named range "Sheets"
With tabs below, this will be the "Sheets" list
"ABC13", XYZ-67","Any_Name"
If ALL the sheets had the same format i.e the name order is constant in the sheets then you could use
=SUM(First:Last!T2)
where First and Last tabs enclose sheets to the summed
Last edited by JohnTopley; 12-19-2021 at 02:35 PM.
I have 52 sheets, 1 per week on varying dates, so named.
here is an example of just 3
Attachment 760108
hopefully this is readable ?
My sheets are named
Jan 8, Feb 23, Mar 30, Dec 31
You already have answers, including sample w/books (see posts #3 and #7)
Thanks John. Didn't work at IBM Hursley did you ?
I did (many years ago!)
Thankyou, all ok now !
I was your PDM (alan N.)
Administrative Note @ Alan N.:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect - 2111 is a release number, not the actual version you have. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks