Auto Calculte Family Members in one Family

[Nitin Shardul]

Dear Sir,

I have attached a file, there are 1000 member with various numbers in family
Is there any formula to derive this solution 2A + 2C, 1A + 2C etc
Please check attached file
Thanks
Nitin

 

[Hui]

I doubt it using the information you supplied

Even if you had the Full Surname there is nothing to say that 20 Smith's are all in the same Smith family, which is unlikely

 

[p45cal]

There is woefully little information to go on; we can guess, wrongly of course:
E code.. is that one family?
A = Adult, C = Child?
Adult = Father, Mother, Spouse, Self(?)
Child = Daughter, Son, Self(?)

or is it based on Date of Birth, maybe Slab, perhaps Age, who the *&^%#! knows?

A wild and inaccurate guess (no countifs as currently only have Excel 2003 where I am):

=SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Spouse")+($H$5:$H$1669="Self")+($H$5:$H$1669="Father")+($H$5:$H$1669="Mother")) & "A + " & SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Daughter")+($H$5:$H$1669="Son")) & "C"

 

[Nitin Shardul]

Dear Sir,
Employee Code is unique for every code No of Family members are different
also attached explanation

 

[bosco_yip]

Please see attached file.

Regards
Bosco

 

[Nitin Shardul]

Thank you very Much Sir,
but Formula Bounced to me
Can you please calculate rate

 

[Nitin Shardul]

Yes Sir,
It Works Thank you

There is woefully little information to go on; we can guess, wrongly of course:
E code.. is that one family?
A = Adult, C = Child?
Adult = Father, Mother, Spouse, Self(?)
Child = Daughter, Son, Self(?)

or is it based on Date of Birth, maybe Slab, perhaps Age, who the *&^%#! knows?

A wild and inaccurate guess (no countifs as currently only have Excel 2003 where I am):

=SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Spouse")+($H$5:$H$1669="Self")+($H$5:$H$1669="Father")+($H$5:$H$1669="Mother")) & "A + " & SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Daughter")+($H$5:$H$1669="Son")) & "C"

 

[Nitin Shardul]

Tried this to calculate rate : =SUMPRODUCT(('Rate Chart'!$B$5:$BD$5=Data!I5)*'Rate Chart'!$AG$6:$BD$6=Data!M5=Data!G5)*'Rate Chart'!A7:BD19

There is woefully little information to go on; we can guess, wrongly of course:
E code.. is that one family?
A = Adult, C = Child?
Adult = Father, Mother, Spouse, Self(?)
Child = Daughter, Son, Self(?)

or is it based on Date of Birth, maybe Slab, perhaps Age, who the *&^%#! knows?

A wild and inaccurate guess (no countifs as currently only have Excel 2003 where I am):

=SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Spouse")+($H$5:$H$1669="Self")+($H$5:$H$1669="Father")+($H$5:$H$1669="Mother")) & "A + " & SUMPRODUCT(--($B5=$B$5:$B$1669),($H$5:$H$1669="Daughter")+($H$5:$H$1669="Son")) & "C"