Hi Ellen

I'm not sure that I fully understand the tests you are wanting to make, but,
IF you are saying the person can only appear once in a row, but it can be in
columns BJ, BQ, BX, CE or CL I would use another column as a helper column
with
=--(OR(BJ13="Teena",BQ13="Teena",BX13="Teena",CE13="T eena",CL13="Teena"))
This will result in 1 for True and 0 of False.
Copy down through rows to row 265

Use your other Sumproduct formulae parts, and just add this new helper
column to deal with whether Teena appears in any of the Columns.

Incidentally, the double unary minus (--) does not act as an AND. It is the
thing which coerces the True or False result to 1 or 0.

If instead of using the , as the separator between expressions in the
Sumproduct formula you use the "*", that acts like an AND, and the "+"
character acts as an OR
--
Regards
Roger Govier

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