Forum Discussion
Formula based on value on another column
analysta
Let's assumed that the numbers of your columns are the actual column numbers:
K=11, L=12 and so on...
Here's the formula:
=IF(A2="Officer",
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))
it will check if cell A2 is "Officer", then the 1.5 multiplication will be applied when it's "Officer", the multiplication of 2 will be applied if it's not "Officer"
analysta
Let's assumed that the numbers of your columns are the actual column numbers:
K=11, L=12 and so on...
Here's the formula:
=IF(A2="Officer",
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))
it will check if cell A2 is "Officer", then the 1.5 multiplication will be applied when it's "Officer", the multiplication of 2 will be applied if it's not "Officer"
Rodrigo_ Thank you! If I wanted to add an additional condition like if A2 contains "Officer" and location equals "Houston". I have tried playing around with this but not getting the expected results.
- Assumed that location/area is on column B.
=IF(AND(A2="Officer", B2="Houston"),
1.5*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*1.5*SUM(K2:L2) + 1.5*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2),
2.0*SUM(K2:L2) + SUM(N2:O2)/100*M2 + SUM(N2:O2)/100*2.0*SUM(K2:L2) + 2.0*R2 + SUM(M2, P2, Q2, S2, T2, U2, V2))
- Thank you! If I wanted to add an additional condition like if A2 contains "Officer" and location equals Houston. I have tried playing around with this but not getting the expected results.
