No announcement yet.

Sum Cells in Multiple Worksheets at Different Positions

  • Filter
  • Time
  • Show
Clear All
new posts

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


  • #2
    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.


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

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


    • #3
      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:


      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.




      • #4
        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?


        • #5

          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).