I have amended the formula slightly to fit my data set and it is working
I am currently using:
=SUMPRODUCT((MONTH('L&D Consultant'!E2:BA2)=MONTH(F1))*('L&D Consultant'!D10:D16=D2),'L&D Consultant'!E10:BA16)
L&D Consultant is the name of the tab my data is sat in. The formula is sat in a dfferent tab (called 'Calcs') and is looking at cell F1 to obtain the month (i've added in month() here as this cell contains a full date) and cell D2 as this contains the unique ID i want to look up.
My data is split out across mutliple sheets depending on the role type of employees. So for example, there might be one tab called 'L&D Consultant' and one called 'Manual Labourer' etc. Ideally I'd like the formula to automatically pick up the role type (the role type is specified in the calcs tab next to the unique id) and use this to determine the name of the sheet it looks in. Is there a way to do this other than using a nested if?
Also the range area for each role type is different, i.e.the data range on the L&D Consultant sheet is E10:BA16 but in the Manual Labourer sheet it might be E10:BA100. Is there a way to get the formula to take this in to account? I'm looking to remove as much manual intervention as possible. The workbook will be updated regularly and the range sizes will change. Each sheet may also have more than one set of data within it for each role within the role type i.e. L&D Consultant may contain 2 or 3 roles, each role has a set of data within the L&D Consultant sheet but each set if data is seperated by several blank rows. So, depending on the role, the range will need to be different in the formula
Does that make any sense?