| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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) "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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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. Bob Phillips wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct
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!D13:D265<"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) -- Biff Microsoft Excel MVP "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) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 needed to understand SUMPRODUCT better in order 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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| SUMPRODUCT - Help | Excel Worksheet Functions | |||
| sumproduct | Excel Worksheet Functions | |||
| Sumproduct ?? | Excel Worksheet Functions | |||
| Sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
