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)