|
Formula Help
I need to see if there is an easier or shorter way to write this formula
=SUMPRODUCT((TERMS!$L$2:$L$18985="2110220821")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220820")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220819")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"})) |
Formula Help
Put all the variables in cells:
A1:A3 = 2110220819; 2110220820; 2110220821 Formatted as TEXT since you're expressing those as TEXT values in your formula B1 = Resigned C1 = 4 Better Job / Pay D1:D12 = JAN07; FEB07; MAR07; ..... DEC07 Then: =SUMPRODUCT(--(ISNUMBER(MATCH(TERMS!$L$2:$L$18985,A1:A3,0))), --(TERMS!$R$2:$R$18985=B1),--(TERMS!$S$2:$S$18985=C1), --(ISNUMBER(MATCH(TERMS!$I$2:$I$18985,D1:D12,0)))) Biff "Scott@CW" wrote in message ... I need to see if there is an easier or shorter way to write this formula =SUMPRODUCT((TERMS!$L$2:$L$18985="2110220821")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220820")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220819")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"})) |
Formula Help
I will give it a shot. Thank you.
"T. Valko" wrote: Put all the variables in cells: A1:A3 = 2110220819; 2110220820; 2110220821 Formatted as TEXT since you're expressing those as TEXT values in your formula B1 = Resigned C1 = 4 Better Job / Pay D1:D12 = JAN07; FEB07; MAR07; ..... DEC07 Then: =SUMPRODUCT(--(ISNUMBER(MATCH(TERMS!$L$2:$L$18985,A1:A3,0))), --(TERMS!$R$2:$R$18985=B1),--(TERMS!$S$2:$S$18985=C1), --(ISNUMBER(MATCH(TERMS!$I$2:$I$18985,D1:D12,0)))) Biff "Scott@CW" wrote in message ... I need to see if there is an easier or shorter way to write this formula =SUMPRODUCT((TERMS!$L$2:$L$18985="2110220821")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220820")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220819")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"})) |
Formula Help
You're welcome!
Biff "Scott@CW" wrote in message ... I will give it a shot. Thank you. "T. Valko" wrote: Put all the variables in cells: A1:A3 = 2110220819; 2110220820; 2110220821 Formatted as TEXT since you're expressing those as TEXT values in your formula B1 = Resigned C1 = 4 Better Job / Pay D1:D12 = JAN07; FEB07; MAR07; ..... DEC07 Then: =SUMPRODUCT(--(ISNUMBER(MATCH(TERMS!$L$2:$L$18985,A1:A3,0))), --(TERMS!$R$2:$R$18985=B1),--(TERMS!$S$2:$S$18985=C1), --(ISNUMBER(MATCH(TERMS!$I$2:$I$18985,D1:D12,0)))) Biff "Scott@CW" wrote in message ... I need to see if there is an easier or shorter way to write this formula =SUMPRODUCT((TERMS!$L$2:$L$18985="2110220821")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220820")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"}))+SUMPRODUCT((TERMS!$L$2: $L$18985="2110220819")*(TERMS!$R$2:$R$18985="Resig ned")*(TERMS!$S$2:$S$18985="4. Better Job / Pay")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07 ","APR07","MAY07","JUN07","JUL07","AUG07","SEP07", "OCT07","NOV07","DEC07"})) |
| All times are GMT +1. The time now is 05:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com