• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Calculating totals across multiple workbooks|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Calculating totals across multiple workbooks|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Calculating totals across multiple …
sp_PrintTopic sp_TopicIcon
Calculating totals across multiple workbooks
Page: 12Jump to page
Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
1
October 29, 2017 - 7:40 pm
sp_Permalink sp_Print sp_EditHistory

 Hi, I'm looking for some help on calculating totals across multiple workbooks. I get the concept of =SUMPRODUCT(SUMIF(INDIRECT( but I can't seem to put it into practice in excel.

I have attached a sample workbook & I'm looking to calculate the number of sick days by employee & day of week per quarter (& eventually annually). The tables in my actual analysis are not in the same cells/boxes in each tab.

Really appreciate if someone could give me a hand.

Thanks

Andy, UK

Avatar
James Niven
Member
Members
Level 0
Forum Posts: 16
Member Since:
October 25, 2013
sp_UserOfflineSmall Offline
2
October 30, 2017 - 11:09 am
sp_Permalink sp_Print

Hi Andy,

Firstly, welcome to this great forum, you will find great help and knowledge here.

I had to rearrange your tables on each sheet as per the January tab, it was the only way I was able to get this to work as you wanted.

Please review my attachment and see if this is the result you are looking for.

Good luck with your project.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
October 30, 2017 - 11:27 am
sp_Permalink sp_Print

Hi Andy

As James said above, it is best if you can rearrange your data properly. Otherwise the formulas can get very messy (as per attachment).

Good luck.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
4
October 31, 2017 - 12:02 am
sp_Permalink sp_Print

Hi Sunny/James, thanks for your help. James, I tried your formula on my actual document & it worked great.

I have a further area of development/automation but I'm not sure if it is possible. I'm looking for employee names to be instantly populated in each monthly worksheet from a central worksheet as new one's start & other's leave. I have an admin who will be completing this and I want to try make it as easy and useable as possible. I guess at a basic level it would be a staff database with the start month and a leave month, therefore if they leave in October they are not included in November's onwards. The number of employees I have is not going to exceed 20, therefore it is possible to keep all the active employees names in the same 20 cells in each monthly worksheet.

If it is possible I'd really appreciate it if you could provide a sample worksheet on this.

Thanks

Andy

Avatar
James Niven
Member
Members
Level 0
Forum Posts: 16
Member Since:
October 25, 2013
sp_UserOfflineSmall Offline
5
November 1, 2017 - 3:15 am
sp_Permalink sp_Print

Andy,

I am glad the formula worked for you and gave the results you are looking for.

As for your question on the staff database, I have not come up with a working solution as yet, but looking into a solution ... but no luck as yet!

I am guessing a index and match with if formula thrown in .....

Anyone else have a solution and possible angle...??

Thanks

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
6
November 10, 2017 - 3:53 am
sp_Permalink sp_Print

Hi James/anyone, any thoughts on previous post? Is it possible?

Andy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
November 10, 2017 - 12:14 pm
sp_Permalink sp_Print sp_EditHistory

Hi Andy

Give this a try. Haven't 100% tested though. Please do not touch row 1 of the Data sheet but you can hide it.

You will then need to use the formulas given by James to consolidate the result.

Hope this helps.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
8
November 11, 2017 - 10:02 am
sp_Permalink sp_Print

Hi Sunny, thanks for doing that, looks pretty impressive to me. I think it has to ability to work, the data tab definitely works. Only thing is, when I remove the { } at the start/end of the formula's in the month tabs or copy the formula to my worksheet (which has to remove the { } signs) it goes blank.... not sure why & the formula doesn't work. Any ideas why that happens?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
November 11, 2017 - 10:35 am
sp_Permalink sp_Print

Hi Andy

Those {} belongs to array formulas. You don't key them in.

When you enter or edit such formulas, you need to press CTRL+SHIFT+ENTER instead of ENTER.

The {} will be automatically inserted.

You can read more about array formulas here https://www.myonlinetraininghu.....ay-formula

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
November 13, 2017 - 11:05 am
sp_Permalink sp_Print

Hi Andy

In addition to the link above, you can also refer to this : https://www.myonlinetraininghu.....om-a-range

This link contains the original code that I modified to remove blanks from the name list.

My codes can still be shorten by removing the part +ISERROR(Table14[JAN]), +ISERROR(Table14[FEB]) etc as I was using it to remove both blank and error cells.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
11
January 22, 2018 - 5:56 am
sp_Permalink sp_Print

Hi guys, I received some previous help on the development of a spreadsheet (see above). All works great.

On Sunny's 'Andy2' workbook I'm looking to extend the same principles on employee names instantly being populated in each monthly worksheet to a quarterly version. So if an employee is with us for Q1 (Jan - March), they automatically populate in an equivalent Q1 worksheet...& so on.

Any help would be appreciated.

Thanks

Andy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
January 22, 2018 - 12:17 pm
sp_Permalink sp_Print

Hi Andy

Give this a try. I have use some helper columns to get the data and then referred to them from the Quarterly sheets.

Hope this helps.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
13
January 28, 2018 - 4:38 am
sp_Permalink sp_Print

Thanks Sunny, works perfectly. 

Last thing I need to analyse/collect is number of sick days across the year in an 'annual' tab. I think it's more complex because the names of the staff will potentially differ in order, in each quarterly tab, depending on when they start/leave. Is this possible to collect? Kind of vital really so I have an annual overview.

Thanks

Andy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
14
January 29, 2018 - 11:21 am
sp_Permalink sp_Print

Hi Andy

You can try this. It is a bit more complex as I don't know how many unique staff are there in a year. I have catered for 40. You can amend it if necessary.

Hope this helps.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
15
March 30, 2018 - 6:20 pm
sp_Permalink sp_Print sp_EditHistory

Hi Sunny, thank you for your expertise on this. The sickness tracker is working really well & I've learned a lot from it also.

I'm trying to add an automatic calculation for staff turnover during the year but I'm not 100% how to do it. The premise would be something along the lines of:

IF > the employee has a 'leave date' inputted, then add up all the contracted hours for all employees who leave in a year & divide by a given number, say 500 (I can put a formula in to work this out this number)

There isn't a column from contract hours in 'Andy 4' so just put a notional 36hrs for all of them.

Thanks

Andy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
16
March 31, 2018 - 10:22 am
sp_Permalink sp_Print

Hi Andy

Can you give an example on how to calculate the contracted hours for one employee? What is the expected result?

The notional 36 hrs is for a week/month? What if an employee join/leave in the middle of a month? Will you use a full month to calculate?

Looking back at my previous solution, you can simplify column V of the Data sheet by using =IFERROR(Table14[@[STAFF NAME]],"") instead of the original formula. Sometimes I tend to make simple things complicated when I think too much Laugh

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
17
April 1, 2018 - 6:26 am
sp_Permalink sp_Print

Hi Sunny, don't worry about calculating the contracted hours, this will be input in the data tab when the employee starts

I don't think it needs a complicated formula. If an employee has a leave date then sum these contracted hours together. I should then be able to add the average number of monthly (worked) hours calculation.

The calculation for staff turnover I'm trying to obtain is: Number of staff hours who have left / the average number of worked hours per month

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
18
April 2, 2018 - 10:16 am
sp_Permalink sp_Print

Hi Andy

Hope I understood you correctly. This is what I am doing.

If there is a date left, checking to see how many months within the year did the staff work.

Multiply that number by 36 hours (since you don't have the actual figures, otherwise just add them)

Hope this is correct.

Sunny

Avatar
Andy Bown
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 29, 2017
sp_UserOfflineSmall Offline
19
April 2, 2018 - 7:06 pm
sp_Permalink sp_Print sp_EditHistory

Hi Sunny, less complicated that that. I have created a new tab in the attached (sheet 1) to make it clearer.

If there is a leave date, sum all these contracted hours across all the employees that have left (regardless of the leave date). No multiplying of 36 needed. 

Sorry I'm making this confusing for you lol

Andy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
20
April 3, 2018 - 9:13 am
sp_Permalink sp_Print

Hi Andy

Maybe I am not too clear what you wanted. You want to sum all contracted hours of all employees that have left.

Try this on sheet1 =SUMIFS(tbl_4[Contracted hours],tbl_4[LEFT],">0").

You should get 252. If this is not correct, then let me know what is the expected result from sheet1.

Sunny

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Shanna Getschel, Ryan Marceau, Darrell Hodge, Mike Green
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31908
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.