Input Variables to calcluate column value

[hstrode]

Hello!  I'm currently creating a goals app in which i have several input variables.  On one sheet, I have each of the sales territories with an input variable that is typed in by user.  That's 21 input variables that I'd like to put into one column based on their selection.  I can get one variable to display based on 

IF(IF(CombinedAgencyFieldAreaNo=$(Field Area), $(Field Area Growth %), column(5))>0, IF(CombinedAgencyFieldAreaNo=$(Field Area), $(Field Area Growth %), column(5)), .02)

but when i try to add $(Field Area 2) and $(Field Area 2 Growth %), it errors or displays $0.

Any help would be greatly appreciated!

[hstrode · Accepted solution]

For anyone who needs this in the future, I solved it with multiple trials and errors.  Overall, the base formula looks like

SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 1)'}, StateAlphaCd = {'$(State)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 1)')

For each of the 10 input field sets, I just added them all together.  So, a little somethikng like:

(SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 1)'}, StateAlphaCd = {'$(State)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 1)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product2)'}, StateAlphaCd = {'$(State2)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 2)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 3)'}, StateAlphaCd = {'$(State 3)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 3)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 4)'}, StateAlphaCd = {'$(State 4)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 4)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 5)'}, StateAlphaCd = {'$(State 5)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 5)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 6)'}, StateAlphaCd = {'$(State 6)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 6)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 7)'}, StateAlphaCd = {'$(State 7)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 7)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 8)'}, StateAlphaCd = {'$(State 8)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 8)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 9)'}, StateAlphaCd = {'$(State 9)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 9)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 10)'}, StateAlphaCd = {'$(State 10)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 10)'))