SumProduct
Thanks to everyone that posted a reply. Combining input from each of you
helped immensely. My VERY LONG formula became MUCH shorter. I just need to
understand SUMPRODUCT better inorder to consolidate some of my redundant
testing.
THANKS SO MUCH!!
Ellen
"Pete_UK" wrote:
You could try it like this:
=SUMPRODUCT(((Jan!BC13:Jan!BC265="Teena")+(Jan!BJ1 3:Jan!BJ265="Teena")+(Jan!BQ13:Jan!BQ265="Teena")+ (Jan!BX13:Jan!BX265="Teena")+(Jan!CE13:Jan!CE265=" Teena")+(Jan!CL13:Jan!CL265="Teena"))*(Jan!D13:Jan !D265<"Direct
Hire")*(Jan!N13:Jan!N265=Jan!Q1)*(Jan!N13:Jan!N26 5<=Jan!Q1+6),Jan!$EC$13:Jan!$EC$265,Jan!$BE$13:Jan !$BE$265)
The * is equivalent to AND, the + is equivalent to OR.
You have a mixture of absolute and relative addresses, so you may need to
change these if you intend to copy the formula elsewhere. For example, you
could put Teena in a cell somewhere and change those references to Teena to
the cell, and then with other names in other cells in the same column, you
could copy the formula down.
Hope this helps.
Pete
"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)
|