Formula Help

G

Guest

The formula below works, I was just wondering there is a shorter way of
writing it.

=SUMPRODUCT((TERMS!\$L\$2:\$L\$18985="3000080600")*(TERMS!\$R\$2:\$R\$18985="Resigned")*(TERMS!\$S\$2:\$S\$18985="9.
Personal /
Other")*(TERMS!\$I\$2:\$I\$18985={"JAN07","FEB07","MAR07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"})+SUMPRODUCT((TERMS!\$L\$2:\$L\$18985="3000080600")*(TERMS!\$R\$2:\$R\$18985="Resigned")*(TERMS!\$S\$2:\$S\$18985="12.
Retirement")*(TERMS!\$I\$2:\$I\$18985={"JAN07","FEB07","MAR07","APR07","MAY07","JUN07","JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"})))

G

Guest

One possibility: use a pivot table?

Dave

G

Guest

(TERMS!\$I\$2:\$I\$18985={"JAN07","FEB07","MAR07","APR07","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")

G

Guest

I wish I could however this in one cell in a rather large workbook.

G

Guest

thanks, I will give a try.

Teethless mama said:
(TERMS!\$I\$2:\$I\$18985={"JAN07","FEB07","MAR07","APR07","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")