Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Sum Cells in Multiple Worksheets at Different Positions

  1. #1
    Join Date
    4th May 2011
    Posts
    2

    Sum Cells in Multiple Worksheets at Different Positions

    Hey everyone - 1st time poster here,

    I have a question about summing a group of cells in multiple worksheets. I have multiple worksheets, each representing a different company. In each of these worksheets is a list of accounts (with account numbers and names in the same cell and the amount in the next column:

    e.g., 121100 Cash 100,000
    121101 Cash Equivalents 50,000
    and so on . . .

    The account coding represents the type of account and the number of accounts is not consistent in each workbook - but the formatting is.

    In one fell swoop, I would like to be able to sum the values of all the accounts starting with "121*" from each tab. Using =SUM('Sheet1:Sheet5'!B3:B10) (<-- example) wouldn't work because each worksheet has the accounts I'm looking for at different positions down the list.

    Any thoughts on how I can do this?

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,096

    Re: Sum Cells in Multiple Worksheets at Different Positions

    Hello and welcome to Ozgrid.

    There is almost always more than one way to get the job done.
    Here is one approach you can try adapting:

    For discussion, we'll assume each data sheet holds a two-column table in the range of A1:B100.

    Row-1 is a header row, where A1 = "Account" and B1 = "Amount"

    This approach uses the DSUM function.
    See also: Dynamic Reporting of an Excel Table

    To keep this post as brief as possible, read the Excel Help on the details of how to use DSUM.

    If F1:G2 are used as the criteria cells for the DSUM and our DSUM formula is placed in the same cell on each of the data sheets, then you can use the formula you showed in your post.

    =SUM('Sheet1:Sheet5'!H1)

    Where cell H1 is the DSUM formula cell on each sheet.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    1st December 2006
    Posts
    1,047

    Re: Sum Cells in Multiple Worksheets at Different Positions

    Hi ATLGator,

    Welcome to the OzGrid community!!

    Unfortunately there is no native excel "3D SUMIF" function to do what you're after. If there's only 5 tabs to sum as per your posted example above, this will work:

    =SUMIF(Sheet1!A3:A10,"121*",Sheet1!B3:B10)+SUMIF(Sheet2!A3:A10,"121*",Sheet2!B3:B10)+SUMIF(Sheet3!A3:A10,"121*",Sheet3!B3:B10)+SUMIF(Sheet4!A3:A10,"121*",Sheet4!B3:B10)+SUMIF(Sheet5!A3:A10,"121*",Sheet5!B3:B10)

    If there's more tabs, you could use the same set of cells on each of the tabs to house a SUMIF formula for each account range and then use the 3D SUM formula you're currently using to sum these.

    Another alternative is use a User Defined Function (like this one) but this may impact on performance for large complex workbooks and/or may create other issues.

    HTH

    Robert

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th May 2011
    Posts
    2

    Re: Sum Cells in Multiple Worksheets at Different Positions

    AAE - Thank you for your help, but I am trying to sum all the accounts in all the worksheets in one 'Summary' cell without summing on each individual worksheet.

    Trebor76 - Dang! I was hoping that wasn't the case. The workbook has about 20 tabs, but I will need to sum more than one type of account in this way - and there are other workbooks. I was thnking something like:

    =SUMPRODUCT(('Sheet1:Sheet3'!A:A)*('Sheet1:Sheet3'!A:A="121*")*('Sheet1:Sheet3'!B:B)) (<--I am using the 3 sheets to test my solutions)

    - but this isn't working for me. I was hoping that by posting my attempts, it might trigger an idea. Any other ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st December 2006
    Posts
    1,047

    Re: Sum Cells in Multiple Worksheets at Different Positions

    You can try Domenic's INDIRECT formula solution from here then, but just bear in mind that each of the tabs will either need to have a space in them or not to govern whether there's a single apostrophe in the suggested formula or not (respectively). Also the INDIRECT function is best suited to the active workbook as it's bound to be open (the INDIRECT function returns #REF! to a closed external reference).

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Link Cells In Multiple Worksheets?
    By kemrich in forum EXCEL HELP
    Replies: 1
    Last Post: May 22nd, 2007, 05:25
  2. Sum Of Products Of Cells In Multiple Worksheets
    By clickmrmike in forum EXCEL HELP
    Replies: 6
    Last Post: January 23rd, 2007, 02:41
  3. locking cells in multiple worksheets
    By colez in forum EXCEL HELP
    Replies: 1
    Last Post: July 11th, 2006, 02:26
  4. Summing cells in multiple worksheets
    By L.B. in forum EXCEL HELP
    Replies: 2
    Last Post: May 25th, 2004, 22:40
  5. Replies: 2
    Last Post: February 19th, 2004, 17:37

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
  •  
porno