Sumif Accross Multiple worksheets

[harrysccs]

Hello,

I have multiple projects worksheets and various employees work on more then one project. I want to sum their weekly hours by various projects into the worksheet called "Consolidated worksheet".

In the attached spreadsheet - if I need to calculate total hours for week 10 Feb 14 in cell F12 - by adding all worksheets highlighted in green - which matches employee in cell C12 of "consolidated worksheet" - how I do this??

If you need any clarification - please let me know.

Thanks.

Harry.

[aydeegee]

Hi Harry

I would imagine that will be a nightmare to manage these number of sheets with sumifs all over the place.

My suggestion involves
1 - each employee having the same row on every sheet. (To not show blank rows, in (say) A12 have the formula =if(C12="",Hide","") Copy down then filter on A
2 - Have a blank sheet called "Start" and a blank sheet called "End". The Start will go just before Bendigo VDI and the End .... (use your imagination )

As an example, using employee Akinloye, Temitope Akindele who will always be on row 12, the formula in F12 will be =SUM(Start:End!F12). As long as any new projects are placed between the Start sheet and the End sheet, the formula will not need to be amended

Hope you find this simple technique useful.

Regards
Alastair

[p24leclerc]

Look at the attached workbook.
I had to create a tab named Projects and a named range named Project_Sheets which contains the names of every projects sheets. You'll have to maintain this sheet un to date with your new project sheets. It is used in the formula in "Consolidated Worksheet".
I was not able, so far, to get the formula to be copied from column to column without manual modification. It can be copied down the row without any problem though. When tou copied a formula to another column, you must change the column letter at the end of the formula manually. As an example you'll change F:F to G:G.

Also, note the this formula is an array formula. it means you have to press CTRL+SHIFT+ENTER at the same time when you leave the edit mode on one of them. You'll see brackets around the formula which means an array formula.

Hope this helps. It is, at least, a start.

[p24leclerc]

here is how you can modify the formula so they will copy from column to column without manual modification:

Please Login or Register  to view this content.

Do not forget to press CTRL+SHIFT+ENTER when you are done entering the formula.
It supposes that the columns from all the project sheets are always synchro in date

[harrysccs]

Thanks Guys - this is really helpful.

Sorry for late reply.

Harry.

[harrysccs]

here is how you can modify the formula so they will copy from column to column without manual modification:

Please Login or Register  to view this content.

Do not forget to press CTRL+SHIFT+ENTER when you are done entering the formula.
It supposes that the columns from all the project sheets are always synchro in date

Hello,

Please note that this formulla is not showing results in "Consolidated Worksheet" - when I opened it was all showing results - but after I run macro in "Master Financial Report" - it was not showing results.

Can you please help??

Harry.

[p24leclerc]

Look in the Named Manager window under the Formulas menu and make sure the named range "Project_Sheets" has the following formula:

Please Login or Register  to view this content.

In the file you supplied, this formula was showing Ref! so the error in the Consolidated Worksheet.
I ran the macro in the Master Financial Report and it does not seem to interfere.
Can't tell why it was like that.

[harrysccs]

Thanks -this is resolved.