KayJay0618
New Member
- Joined
- Jul 20, 2016
- Messages
- 40
I have an Excel 2010 worksheet tracking homeless client data for a cold weather shelter for families. Each family is assigned a number. If there are 5 members in the family, they will all have the same unique family number. The Family # is in column D of the spreadsheet. One of the family members is designated as Household Head with a Y in column G. Only one family member will have a Y in this column. If any of the family members have an income of any kind, that annual income amount is entered in column W. In column X on the row with a Y in column H, I need a formula that will sum the income for that unique family. Sample data below. The last column is what the total should be.[TABLE="width: 1266"]
<tbody>[TR]
[TD]Month of Service[/TD]
[TD]Date Entered Shelter[/TD]
[TD]Date Exited Shelter[/TD]
[TD]Family #[/TD]
[TD]First Name [/TD]
[TD]Last Name[/TD]
[TD]Head of House[/TD]
[TD]Single Parent[/TD]
[TD]Sex[/TD]
[TD]Age[/TD]
[TD]Household Number[/TD]
[TD]-18[/TD]
[TD]+18[/TD]
[TD]Disabled[/TD]
[TD]Veteran[/TD]
[TD]Case Status[/TD]
[TD]Ongoing Status[/TD]
[TD]Main Source of Income[/TD]
[TD]Source of Income-2[/TD]
[TD]Source of Income-3[/TD]
[TD]Source of Income-4[/TD]
[TD]Monthly Income[/TD]
[TD]Yearly Income[/TD]
[TD]Total Family Income[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/1/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]32[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 22,000.00 [/TD]
[TD]30,772.00[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/2/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Cindy[/TD]
[TD]Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SSD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 8,772.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/3/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Joe[/TD]
[TD]Smith[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]28[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 37,380.72 [/TD]
[TD]52,380.72[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/4/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/5/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]****[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/6/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$5,000.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/7/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Billy[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 10,000.00 [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD]Month of Service[/TD]
[TD]Date Entered Shelter[/TD]
[TD]Date Exited Shelter[/TD]
[TD]Family #[/TD]
[TD]First Name [/TD]
[TD]Last Name[/TD]
[TD]Head of House[/TD]
[TD]Single Parent[/TD]
[TD]Sex[/TD]
[TD]Age[/TD]
[TD]Household Number[/TD]
[TD]-18[/TD]
[TD]+18[/TD]
[TD]Disabled[/TD]
[TD]Veteran[/TD]
[TD]Case Status[/TD]
[TD]Ongoing Status[/TD]
[TD]Main Source of Income[/TD]
[TD]Source of Income-2[/TD]
[TD]Source of Income-3[/TD]
[TD]Source of Income-4[/TD]
[TD]Monthly Income[/TD]
[TD]Yearly Income[/TD]
[TD]Total Family Income[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/1/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]32[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 22,000.00 [/TD]
[TD]30,772.00[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/2/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Cindy[/TD]
[TD]Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SSD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 8,772.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/3/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Joe[/TD]
[TD]Smith[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]28[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 37,380.72 [/TD]
[TD]52,380.72[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/4/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/5/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]****[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/6/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$5,000.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]7/7/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Billy[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 10,000.00 [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]