I would start by trying this.

Select say cells Z13:Z265 and enter this formula in the formula bar

IF((Jan!BC13:Jan!BC265="Tenna")*(Jan!D13:Jan!D265< "Direct Hire")*
(Jan!$N$13:Jan!$N$265=Jan!Q1)*(Jan!$N$13:Jan!$N$2 65<=Jan!Q1+6),
Jan!$EC$13:Jan!$EC$265)

and array enter it.

Then in another cell use

=SUMPRODUCT(Z13:Z265,Jan!$BE$13:Jan!$BE$265)
+SUMPRODUCT(Z13:Z265,Jan!$BL$13:Jan!$BL$265)
+SUMPRODUCT(Z13:Z265,Jan!$BS$13:Jan!$BS$265)
+SUMPRODUCT(Z13:Z265,Jan!$BZ$13:Jan!$BZ$265)
+SUMPRODUCT(Z13:Z265,Jan!$CG$13:Jan!$CG$265)
+SUMPRODUCT(Z13:Z265,Jan!$CN$13:Jan!$CN$265)


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ellen G" wrote in message
...
Hi All --

I am using a SumProduct formula that is VERY LONG due to the number of
items
I need to verify. Too long actually for Excel to handle.

Could someone educate me on some additional ways to use SumProduct that
might reduce my formula? For example, I know that using the double dash
(--)
tests for certain qualifications and acts essentially like an AND. Is
their a
way to test and act like an OR?

Also, I need to add several SumProducts together, each including some of
the
same tests. Is there a way to set qualifying tests for ALL of the
sumproducts
that I am adding together? Below is an example of what I mean

Thanks for any input you can provide.

Ellen

A person is assigned to an individual transaction in my sheet. However,
they
may be assigned in any one of 6 columns. So, in another sheet I am trying
to
calculate the following:

If this is Teena's transaction: --(Jan!BC13:Jan!BC265="Tenna")
If the transaction date is between 2 dates:
--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6)
If this is not a Direct Hi --(Jan!D13:Jan!D265<"Direct Hire")
Then number of fills x % of ownership:
Jan!$EC$13:Jan!$EC$265,Jan!$BE$13:Jan!$BE$265


Repeat the same testing -- if Teena in column BJ
Repeat the same testing -- if Teena in column BQ
Repeat the same testing -- if Teena in column BX
Repeat the same testing -- if Teena in column CE
Repeat the same testing -- if Teena in column CL

=SUMPRODUCT(--(Jan!BC13:Jan!BC265="Tenna"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$BE$13:Jan!$BE$265)+SUMPRODUCT(--(Jan!BJ13:Jan!BJ265="Teena"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$BL$13:Jan!$BL$265)+SUMPRODUCT(--(Jan!BQ13:Jan!BQ265="Tenna"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$BS$13:Jan!$BS$265)+SUMPRODUCT(--(Jan!BX13:Jan!BX265="Tenna"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$BZ$13:Jan!$BZ$265)+SUMPRODUCT(--(Jan!CE13:Jan!CE265="Tenna"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$CG$13:Jan!$CG$265)+SUMPRODUCT(--(Jan!CL13:Jan!CL265="Tenna"),--(Jan!D13:Jan!D265<"Direct
Hire"),--(Jan!$N$13:Jan!$N$265=Jan!Q1),--(Jan!$N$13:Jan!$N$265<=Jan!Q1+6),Jan!$EC$13:Jan!$E C$265,Jan!$CN$13:Jan!$CN$265)