Hi,
I need assistance to create a formula that will populate the state columns (F - H) with the Balance in Column D by checking:
1. The subacct in the workpaper tab against the data in the Subacct tab.
2. The Subacct tab will have the State associated with the subacct.
For the 50000 in Column D, I want it to populate 50000 in Column F (CA) b/c subacct 200-1000 is in CA.
I've tried SUMIFS but cannot get it to work.
Workpaper Tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Acct[/TD]
[TD]Subacct[/TD]
[TD]Description[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD]CA[/TD]
[TD]ND[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1000[/TD]
[TD]Rental[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1001[/TD]
[TD]Rental[/TD]
[TD]30000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1002[/TD]
[TD]Rental[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1000[/TD]
[TD]Interest[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1001[/TD]
[TD]Interest[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1002[/TD]
[TD]Interest[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Subaccount Tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Subacct[/TD]
[TD]Investment[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]200-1000[/TD]
[TD]Building A[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]200-1001[/TD]
[TD]Building B[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]200-1002[/TD]
[TD]Building C[/TD]
[TD]ND[/TD]
[/TR]
</tbody>[/TABLE]
Thanks!
A
I need assistance to create a formula that will populate the state columns (F - H) with the Balance in Column D by checking:
1. The subacct in the workpaper tab against the data in the Subacct tab.
2. The Subacct tab will have the State associated with the subacct.
For the 50000 in Column D, I want it to populate 50000 in Column F (CA) b/c subacct 200-1000 is in CA.
I've tried SUMIFS but cannot get it to work.
Workpaper Tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Acct[/TD]
[TD]Subacct[/TD]
[TD]Description[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD]CA[/TD]
[TD]ND[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1000[/TD]
[TD]Rental[/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1001[/TD]
[TD]Rental[/TD]
[TD]30000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300000[/TD]
[TD]200-1002[/TD]
[TD]Rental[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1000[/TD]
[TD]Interest[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1001[/TD]
[TD]Interest[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400000[/TD]
[TD]200-1002[/TD]
[TD]Interest[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Subaccount Tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Subacct[/TD]
[TD]Investment[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]200-1000[/TD]
[TD]Building A[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]200-1001[/TD]
[TD]Building B[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]200-1002[/TD]
[TD]Building C[/TD]
[TD]ND[/TD]
[/TR]
</tbody>[/TABLE]
Thanks!
A