Didn't even notice that one. Wood for the trees ...

--
---
HTH

Bob


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



"Dave Peterson" wrote in message
...
Just to add to Bob's response...

You can change references that look like this:
Jan!$BE$13:Jan!$BE$265
to
Jan!$BE$13:$BE$265



Bob Phillips wrote:

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)

--

Dave Peterson