| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
The formula below works, I was just wondering there is a shorter way of
writing it. =SUMPRODUCT((TERMS!$L$2:$L$18985="3000080600")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="9. Personal / Other")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR 07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07 ","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!$L$2 :$L$18985="3000080600")*(TERMS!$R$2:$R$18985="Resi gned")*(TERMS!$S$2:$S$18985="12. Retirement")*(TERMS!$I$2:$I$18985={"JAN07","FEB07" ,"MAR07","APR07","MAY07","JUN07","JUL07","AUG07"," SEP07","OCT07","NOV07","DEC07"}))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help
One possibility: use a pivot table?
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Scott@CW" wrote: The formula below works, I was just wondering there is a shorter way of writing it. =SUMPRODUCT((TERMS!$L$2:$L$18985="3000080600")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="9. Personal / Other")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR 07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07 ","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!$L$2 :$L$18985="3000080600")*(TERMS!$R$2:$R$18985="Resi gned")*(TERMS!$S$2:$S$18985="12. Retirement")*(TERMS!$I$2:$I$18985={"JAN07","FEB07" ,"MAR07","APR07","MAY07","JUN07","JUL07","AUG07"," SEP07","OCT07","NOV07","DEC07"}))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help
I wish I could however this in one cell in a rather large workbook.
"Dave F" wrote: One possibility: use a pivot table? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Scott@CW" wrote: The formula below works, I was just wondering there is a shorter way of writing it. =SUMPRODUCT((TERMS!$L$2:$L$18985="3000080600")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="9. Personal / Other")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR 07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07 ","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!$L$2 :$L$18985="3000080600")*(TERMS!$R$2:$R$18985="Resi gned")*(TERMS!$S$2:$S$18985="12. Retirement")*(TERMS!$I$2:$I$18985={"JAN07","FEB07" ,"MAR07","APR07","MAY07","JUN07","JUL07","AUG07"," SEP07","OCT07","NOV07","DEC07"}))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help
(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07","APR 07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07 ","NOV07","DEC07"})
you can replace the lengthy formula above with the one below (RIGHT(TERMS!$I$2:$I$18985,2)="07") "Scott@CW" wrote: The formula below works, I was just wondering there is a shorter way of writing it. =SUMPRODUCT((TERMS!$L$2:$L$18985="3000080600")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="9. Personal / Other")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR 07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07 ","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!$L$2 :$L$18985="3000080600")*(TERMS!$R$2:$R$18985="Resi gned")*(TERMS!$S$2:$S$18985="12. Retirement")*(TERMS!$I$2:$I$18985={"JAN07","FEB07" ,"MAR07","APR07","MAY07","JUN07","JUL07","AUG07"," SEP07","OCT07","NOV07","DEC07"}))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help
thanks, I will give a try.
"Teethless mama" wrote: (TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR07","APR 07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07 ","NOV07","DEC07"}) you can replace the lengthy formula above with the one below (RIGHT(TERMS!$I$2:$I$18985,2)="07") "Scott@CW" wrote: The formula below works, I was just wondering there is a shorter way of writing it. =SUMPRODUCT((TERMS!$L$2:$L$18985="3000080600")*(TE RMS!$R$2:$R$18985="Resigned")*(TERMS!$S$2:$S$18985 ="9. Personal / Other")*(TERMS!$I$2:$I$18985={"JAN07","FEB07","MAR 07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07 ","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!$L$2 :$L$18985="3000080600")*(TERMS!$R$2:$R$18985="Resi gned")*(TERMS!$S$2:$S$18985="12. Retirement")*(TERMS!$I$2:$I$18985={"JAN07","FEB07" ,"MAR07","APR07","MAY07","JUN07","JUL07","AUG07"," SEP07","OCT07","NOV07","DEC07"}))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
