Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Cells in Multiple Worksheets at Different Positions

1. I agreed to these rules
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. ## 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.

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.

3. OzMVP
Join Date
1st December 2006
Posts
1,193

## 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. I agreed to these rules
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. OzMVP
Join Date
1st December 2006
Posts
1,193

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

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