Log In
Register
Home
October 29, 2017
Offline
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
October 25, 2013
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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
October 25, 2013
Offline
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
October 29, 2017
Offline
Hi James/anyone, any thoughts on previous post? Is it possible?
Andy
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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?
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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 
Sunny
October 29, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
October 29, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
