Excel Sumproduct to Determine Dep Number When There are Twins

[kwtx04rm]

Hello,

Need help with Sumproduct to assign correct Dependent Number when there are twins.
I'm using the formula shown below but it produces two records with a Dep Number = 2.

But how can I get it to produce 2 then 3?

Many thanks in advance!

Formula:

=IF(D2="E",0,IF(D2="S",1,IF(AND(B3<>B2,OR(D2="D",D2="G")),SUMPRODUCT((A$2:A$65536=A2)*(D$2$65536="D")*(C$2:C$65536<C2))+SUMPRODUCT((A$2:A$65536=A2)*(D$2$65536="G")*(C$2:C$65536<C2))+2,"")))

Sample Data and results

EmployeeID First Name DOB Relation Dep Number
123456789 Joe 03/01/1987 D 2
123456789 Kevin 03/01/1987 D 2
123456789 Judy 12/01/2005 G 4
123456789 Cindy 04/17/1964 S 1
123456789 Tony 04/23/1960 E 0

 

[GlennUK]

Can you explain the logic that you are trying to implement in English, rather than letting us try to discern what you are aiming for from the formula?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

I'm guessing here, but here's what it looks like to me.

1. The Order of Relation is
[tt]
RelSeq
E
S
D
G
[/tt]
I'm using Named Ranges.

Add a column for Sequence of the table, with the following formula...
[tt]
=match(D2,RelSeq,0)
[/tt]
and then SORT the table on EmployeeID & Sequence.

The the Dep Number formula is...
[tt]
=IF(A2=A1,E1+1,0)
[/tt]
and the result in the table is...
[tt]
EmployeeID First Name DOB Relation Dep Number Seq
123456789 Tony 4/23/1960 E 0 1
123456789 Cindy 4/17/1964 S 1 2
123456789 Joe 3/1/1987 D 2 3
123456789 Kevin 3/1/1987 D 3 3
123456789 Judy 12/1/2005 G 4 4
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]