SumProduct

[Ellen G]

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 Hire: --(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!$EC$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!$EC$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!$EC$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!$EC$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!$EC$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!$EC$265,Jan!$CN$13:Jan!$CN$265)

 

[Roger Govier]

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="Teena",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>

 

[Bob Phillips]

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$265<=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)

 

[Bob Phillips]

BTW, OR is +, See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
detailed explanation.

--
---
HTH

Bob


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

 

[Dave Peterson]

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

 

[Pete_UK]

You could try it like this:

=SUMPRODUCT(((Jan!BC13:Jan!BC265="Teena")+(Jan!BJ13: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!N265<=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

 

[Bob Phillips]

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]

I saw that, but was afraid to look at what the OP really wanted.

Those long formulas scare me. I'd much rather use multiple helper cells with
easier formulas--like you suggested.

 

[T. Valko]

Another one...

Array entered** :

=SUMPRODUCT(--(MMULT(--(MOD(COLUMN(Jan!BC13:CL265),7)=6)*(Jan!BC13:CL265="Tenna"),TRANSPOSE(COLUMN(Jan!BC13:CL265)^0))>0),--(Jan!N13:N265>=Jan!Q1),--(Jan!N13:N265<=Jan!Q1+6),--(Jan!D13265<>"Direct
Hire"),Jan!EC13:EC265,Jan!BE13:BE265)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ellen G]

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

 

[Ellen G]

Thanks to everyone that posted a reply. Combining input from each of you
helped immensely. My VERY LONG formula became MUCH shorter. I just needed to
understand SUMPRODUCT better in order to consolidate some of my redundant
testing.

THANKS SO MUCH!!

Ellen