Excel Chimp
Board Regular
- Joined
- Oct 30, 2008
- Messages
- 90
Alright, I have a real challenge. Below formula was working until I added the last string, in red. String is red is attempting to do what is described in red text below. I tried to describe this formula's operation in plain English:
If x="per lead", then return Y. If X ="per move in", then return Z. If neither of these if true, then return A. But, if resulting value is greater than T (capped amount), then return T.
=IF(IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Lead",SUMIF(combo1,$C19&$D19&I$7,leads)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Move In",SUMIF(combo1,$C19&$D19&I$7,move_in)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),SUMIF(contractsdata[concombo1],$C19&$D19&I$7,contractsdata[Contract Amount])))>INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),4),INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),4),(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Lead",SUMIF(combo1,$C19&$D19&I$7,leads)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Move In",SUMIF(combo1,$C19&$D19&I$7,move_in)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),SUMIF(contractsdata[concombo1],$C19&$D19&I$7,contractsdata[Contract Amount])))
Help, please! I'm sure I'm doing something completely boneheaded and my formula could probably be a lot more efficent, but I'm no guru.
Thanks.
If x="per lead", then return Y. If X ="per move in", then return Z. If neither of these if true, then return A. But, if resulting value is greater than T (capped amount), then return T.
=IF(IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Lead",SUMIF(combo1,$C19&$D19&I$7,leads)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Move In",SUMIF(combo1,$C19&$D19&I$7,move_in)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),SUMIF(contractsdata[concombo1],$C19&$D19&I$7,contractsdata[Contract Amount])))>INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),4),INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),4),(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Lead",SUMIF(combo1,$C19&$D19&I$7,leads)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),IF(INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),5)="Per Move In",SUMIF(combo1,$C19&$D19&I$7,move_in)*INDEX(contractsdata,MATCH($C19&$D19&I$7,contractsdata[concombo1],0),3),SUMIF(contractsdata[concombo1],$C19&$D19&I$7,contractsdata[Contract Amount])))
Help, please! I'm sure I'm doing something completely boneheaded and my formula could probably be a lot more efficent, but I'm no guru.
Thanks.