# Formula Help

G

#### Guest

I need to see if there is an easier or shorter way to write this formula

=SUMPRODUCT((TERMS!\$L\$2:\$L\$18985="2110220821")*(TERMS!\$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="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="2110220819")*(TERMS!\$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"}))

T

#### T. Valko

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

D112 = 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,D112,0))))

Biff

G

#### Guest

I will give it a shot. Thank you.

T. Valko said:
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

D112 = 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,D112,0))))

Biff