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"})))

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"})))

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"})))

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"})))

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"})))