Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formula totalling across multiple worksheets

Posted on 2013-05-28
3
Medium Priority
?
391 Views
Last Modified: 2013-05-29
Hello Experts, I am hoping someone can help with searching for and totalling values over multiple worksheets -

I have an Excel file with multiple worksheets, each of which records jobs undertaken for a company in a given month.

All sheets exept TOTALS are formatted in the same way:

Column A as the date in 25.06.13 format
Column B as description of job
Column C as cost

The companies worked for vary from month to month and as such the number of worksheets, and their names, varies greatly - the only constant from month to month is the TOTALS worksheet.

I already have totals for each company in a given month however would like a breakdown on the TOTALS sheet of costs delivered on each day of that month, across all companies.

Could this be achieved with a formula?  Or would it have to be a macro?

Either way could someone suggest any code/formulas which may help?
0
Comment
Question by:fourthgen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39202452
Have a look here.

http://www.xl-central.com/sum-single-criteria-multiple-sheets.html

you can also adapt SUMIFS if you have multiple criteria..
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 39202763
The SUM function will sum a specific cell across any number of worksheets given the first worksheet name and the last worksheet name.   As an example, if you had the following worksheets…

TOTALS
First
IBM
HP
Microsoft
Apple
Rosetta Stone
Last

The formula…    =SUM(First:Last!C2) would sum cell C2 from any worksheets that fall between(and including) the worksheets named First and the worksheet named Last.   It could be 5 sheets as indicated above or it could be 50 worksheets.   I used the names First and Last but the beginning and ending worksheets name can be anything you want them to be.   Just make sure the worksheets you want to sum fall between these two boundaries.   A sample file is attached.
Q-28140924.xlsm
0
 

Author Closing Comment

by:fourthgen
ID: 39205639
NB_VC - Thanks for the link, I have adapted the formula and it works perfectly!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Know the reasons and solutions to move/import EDB to New Exchange Server. Also, find out how to recover an Exchange .edb file and to restore the file back.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video discusses moving either the default database or any database to a new volume.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question