October 29, 2017

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

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

October 29, 2017

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

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

VIP

Trusted Members

June 25, 2016

October 29, 2017

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

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

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

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

October 29, 2017

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

October 29, 2017

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

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

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

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

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

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)