Count where end date falls within range, but non-unique records for Vlookup

Good morning,

I have a query I am running into some trouble with. Hoping you can assist.

On the attached table, I am trying to do a sum of the values in column N ++, where the value in column F is falling within a given month. However, if another value in Column A has a matching start date that is end date +1, I do not want it included.

So from the example table .. Jodie Baker is in Rows 10 and 11. on Row 10 she has an end date of 13/3/10, however also has a start date in a different role of 14/3/10 in row 11, so her figure for row 10 in March should not be included in the sum. Given the end date in row 11 is a dummy date set 90 years in the future, hopefully she will have moved on by then

Shane Avenell has data in rows 8 and 9. He should not be counted in April, as there is a starting date on 1 May of a new role. However, the end date in Row 9 of July has no matching start date (End date +1), so this indicates it is a finishing date, and should be included in the sum of people leaving in that month.

The value to be summed is the respective value in the month column, as that indicates an FTE equivalent. A simple count will not return the value we need.

Hoping this explains in enough detail. I am running into problems trying to differentiate between those values where there is a matching start date at end date +1 for a different role, and those where it is a true end date.

Thanks in advance
Darren

Not entirely sure I follow but for the sake of simplicity (and arguably efficiency) I would suggest you do the following (based on my interpretation of your requirements):

1 - create a key of name@startdate

L5: =$A5&"@"&$E5
copied down

2 - revise matrix formula to utilise the key in determining whether or not a given record should be discounted

N5: =AND(N$3>$E5,N$3<$F5)*ISNA(MATCH($A5&"@"&$F5+1,$L$5:$L$24,0))*$D5
copied across matrix

JR makes a good point...

If the data is always sorted first by name and then by start date then my previous approach is guilty of over-engineering and you could perhaps simplify to:

N5: =AND(N$3>$E5,N$3<$F5,OR($A6<>$A5,$E6<>$F5+1))*$D5
applied to matrix
(ie no need for helper key column)

OK, so if I've understood (this time) perhaps:

N27:
=SUMPRODUCT(--((($A$6:$A$25<>$A$5:$A$24)+($E$6:$E$25<>($F$5:$F$24+1)))>0),--(TEXT($F$5:$F$24,"MMYYYY")=TEXT(N$3,"MMYYYY")),N$5:N$24)
copied across to Y27

Not quite, it should be:

=SUMPRODUCT(--((($A$52:$A$338<>$A$51:$A$337)+($E$52:$E$338<>($F$51:$F$337+1)))>0),--(TEXT($F$51:$F$337,"MMYYYY")=TEXT(N$17,"MMYYYY")),N$51:N$337)

The SUMPRODUCT does the following:

1a - compare every name in the data set to the prior name - all names must be processed so range A52:A338 is compared to A51:A337
(note though the ranges are different they share the same dimension of 287x1)

1b - compare every start date in the data set to the prior end date - per the name all rows must be processed so E52:E338 is compared to F51:F337
(note though the ranges are different they share the same dimension of 287x1)

1a output and 1b are added together - ie an OR test.
If either a) the names are different or b) the start date is not = prior end date + 1 then you know this transaction is a "possible" for inclusion


2 - compare the month of the end date to the month of the matrix - all transactions are processed thus F51:F337 dates are compared to header.

3 - identify the FTE apportionment for summation - all rows to be included thus N$51:N$337


The corrected formula in N42 onwards should generate 0;0;3;2;0;1 etc
(there seems to be a spike in Jul-10 but I've not the time to verify I'm afraid)

Post back if you have further problems.

Hi,

Because you have inserted two new rows, your formula in cell N42 should now look like :

Please Login or Register  to view this content.

The sign > has been added to account for the latest request ...

HTH

Originally Posted by Grimace

I have just been requested to match the attrition to the same dates as the headcount (being 15th of each month).

I am assuming if I change the last section of the formula containing the date match of MMYYYY, to be greater than or equal to the previous month in row 17, and less than the current month in row 17, this should amend the count to be in line with the headcount rows?

I am working throught his now, just checking my head is in the right spot? it is post a 4 day weekend after all

thanks
Darren

Darren, I'm not quite sure your approach is correct given present setup.

Using your last sample file - consider Row 88 in which we have a leave date of 18th July.

Presently:

a) your HC matrix will show a 1 in July and 0 in August
this no longer reflects the preferred logic - technically speaking the 1 should appear in August also, no ?

b) given a) it follows that the revised approach in the SUMPRODUCT will ignore the attrition until August - given the matrix does not reflect this (0) the attrition will in essence be ignored.


It seems then, to me at least, that you must first adjust your HC matrix approach such that the values reflect the new logic
that is to say there should be a 1 in August as well as July for row 88

On a final note regards your formula of:

=SUMPRODUCT(--((($A$54:$A$342<>$A$53:$A$341)+($E$54:$E$342<>($F$53:$F$341+1)))>0),--(AND($F$53:$F$341>=M$17,$F$53:$F$341<N$17)),N$53:N$341)

In SUMPRODUCTs / Arrays AND tests are conducted by virtue of multiplication (ORs by virtue of addition) - so your formula should have read:

=SUMPRODUCT(--((($A$54:$A$342<>$A$53:$A$341)+($E$54:$E$342<>($F$53:$F$341+1)))>0),--($F$53:$F$341>=L$17),--($F$53:$F$341<M$17),M$53:M$341)

but as stated this won't be a viable approach until such time as the source is corrected per the revised logic
(you should also adjust to cater for the fact the L17 is blank rather than being 15th Dec 2009 else you could overstate Jan 10 attrition)

Darren, I'm not sure if I was as clear as I had hoped to have been or if I'm failing to understand the significance of your response ?

I understand what you're looking to do. My prior post was merely making the point that IMO you need to first revise your headcount matrix to reflect the 15th logic, presently it does not.

Using your last file I tried to use Row 88 to highlight this fact.

The leave date of 18/07/2010 means that per your revised logic the attrition would not be counted until August 2010 yet your formulae in S88/T88 are such that the final headcount value still appears in July rather than August.
The result of which is that when the attrition is calculated for August and row 88 is identified the resulting headcount value is 0 (T88) - thus in effect the attrition is ignored.

For ex. you could perhaps use:

O53:
=IF(AND(O$52>$E53,O$52<DATE(YEAR($F53),MONTH($F53)+(DAY($F53)>14),16)),$D53,0)
applied to matrix

With the above in place and with the earlier SUMPRODUCT in place, ie:

O42:
=SUMPRODUCT(--((($A$54:$A$342<>$A$53:$A$341)+($E$54:$E$342<>($F$53:$F$341+1)))>0),--($F$53:$F$341>=DATE(YEAR(O$17),MONTH(O$17)-1,DAY(O$17))),--($F$53:$F$341<O$17),O$53:O$341)
copied across

you will get some results... (0,6,2,0,1,25.4 etc...)

Given the dates involved both precede the 15th of the month - which value should persist - 0.7 in O53 or 1 in O54 ?

I will have a think - in reality the current row must check

a) next row to see if the next row is a "continuation" and if so must subsequently check whether the current row ends prior to the 15th

b) prior row to see if the current row is itself a "continuation" and if so est. whether start date precedes the 16th of the month

It should work it will just be a little cumbersome formula wise.

I've not done a lot of testing I'm afraid but I wonder if perhaps you could apply:

M53
=$D53*(($E53<=M$52)-(IF(AND($A54=$A53,$E54=($F53+1)),$F53,DATE(YEAR($F53),MONTH($F53)+(DAY($F53)>15),15))<M$52))
across matrix

does that generate the expected matrix output as you see it ?

Originally Posted by Grimace - earlier post

What I would really like to happen is have the attrition Calc represented as up to and including 15th of this month, but 16th or after the previous month.

Originally Posted by Grimace - prior post

i have identified that if an end date is 16th or greater in the month, it still shows in the following month. eg Row 59 has an end date of 18/07/2010. This should show as a value of 1 in Column S "July" (as the snapshot is taken at 15th of the month), and should not appear in August.

I guess you need to clarify the requirements for my benefit as I confess given the above I'm struggling a little with the underlying logic given it seems inconsistent.

Thus far my understanding is as follows:

Originally Posted by D.O

-- In terms of attrition, where End Date > 15th of month this should be accounted for in the following month

-- In terms of headcount, given the above, I'm a little hazy... are you saying that day of month is of no consequence ?

-- In terms of continuity records precedence is given to whichever record is "active" on the 15th of the cross over month

Perhaps easiest to demo. the logic by means of examples:

Start 1-Mar-2010 | End 13-Jun-2010

From the perspective of the 1/0 matrix - you would expect 1's in Mar, Apr, May, however, for my understanding Jun is the key month - is this 1 or 0 ?

And if the End date were the 18th Jun what then ?

And, finally, if the Start were 20-Mar-2010 would we have 1 or 0 in Mar ?

Presumably all of the rules that you apply to the above would be consistent for continuity records with the previously noted exception regards cross over months:

0.7 | Start 1-Mar-2010 | End 13-Jun-2010
1.0 | Start 14-Jun-2010 | End 31-Jul-2020

where precedence for cross over month is given to whichever record is active on the 15th, in this case the 1.0

Once the logic of the matrix is finalised we can work on creating the attrition calcs - it would seem in retrospect that we can not have the matrix align correctly for both FTE & Attrition - thus the attrition calc. we become slightly convoluted.

Based on your prior post I would suggest:

M53:
=$D53*AND($E53<=M$52,$F53>=M$52)
applied across matrix M53:AH341

Then for the attrition:

N42:
=SUMPRODUCT(--(N$53:N$341=0),--(M$53:M$341>0),--((($A$53:$A$341<>$A$54:$A$342)+($F$53:$F$341<>($E$54:$E$342-1)))>0))
copied across to AH42