I have got a little stuck on a sumproduct query, and know its something basic! Basically I have a Data sheet with information that each month adds in a list of employees. What I want to do is compare the leavers and joiners by department, and the by department is stumping me. If it was a total I could use this:-
So the two data ranges are for April and March - I could then calculate the number of rows for the range, then subtract the number of employees that happen to be in both current month and previous month (and vice versa to give me both joiners and leavers ).
The question therefore is how can I add in the cost centre "Prod" to the calculation (ie I wish to know how many employees that are in cost centre "Prod" are in the current month but weren't there previous month (so must be a joiner), and also then do the reverse of seeing how many employees were in "Prod" previous month but now not found in the current month (ie they must be a leaver) ? I could write vba to do this but I am sure a formula could do the same, just need a nudge!
Thanks in advance