Summing based on multiple conditions

Hi there,

I am looking to create a workbook that shows a total count of hours where workers have been moved.

There are multiple units within an area and the same one unit can be defined within multiple areas.

e.g. Unit 0101 is in Area10 and Area16.

The Workbook contains a mock 'front end' and the other sheets are the month totals that are populated through an export from an Access database.

I am looking to create a loop to iterate all month sheets but as an example I will only describe one month. The process is as follows:

Look at Month1 sheet and get the first unit, then check what areas this unit is in (Adjusted Hours.Range(AB:AB)) and then accumulate the hours associated with that unit to all relevant area totals for that month.

Therefore from the data:

Hours	Unit
-9	0101
3	1101
6	1201
-12	1707
4	7777

Would result in:

Area	Hours
Area 1	3
Area 2	6
Area 4	-12
Area 10	-9
Area 14	4
Area 16	-9
Area 19	4

I have made an attempt at this (see attached example doc) but I could not manage to sum the relevant information.t

I have tried to comment as best I could to describe what i have done/expect to happen.

Any time spent on this would be greatly appreciated!

Thanks,
Greg

Hey Greg

1. Fix your Unit entries-inconsistent...Some are stored as text, others as a number...I corrected this...
2. Rename your sheets to Mar, Apr, May
3. Inserted Area in all blanks of AA:BB lookup table

Then this will work...

Please Login or Register  to view this content.

..........................
Thanks.png