If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add Subtotal functions using cell references
Hello,
I have the following formula that I would like to simplify/improve because when it is complete, it is too long for Excel 2003 I am using: =(SUMPRODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$4)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$5)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$6))) ....etc...etc... This formula interprets "raw data" on monthly worksheets with sheet names in the range C4:C15. The A4:A14 range contains collection center codes under which I am trying to sum for the master collection center. All QA codes (I3:I1000 range on "raw data" sheet) are referenced in $E$2:$AI$2 column headers. I have seached the discussions and have not seen any other solutions. Your help and wisdom are appreciated! -- From there to here, from here to there, funny things are everywhere |
#2
|
|||
|
|||
Add Subtotal functions using cell references
=SUMPRODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),(--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$4)+--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$5)+--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$6)))
Shrinks it a bit.... "JeffB" wrote: Hello, I have the following formula that I would like to simplify/improve because when it is complete, it is too long for Excel 2003 I am using: =(SUMPRODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$4)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$5)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$6))) ...etc...etc... This formula interprets "raw data" on monthly worksheets with sheet names in the range C4:C15. The A4:A14 range contains collection center codes under which I am trying to sum for the master collection center. All QA codes (I3:I1000 range on "raw data" sheet) are referenced in $E$2:$AI$2 column headers. I have seached the discussions and have not seen any other solutions. Your help and wisdom are appreciated! -- From there to here, from here to there, funny things are everywhere |
#3
|
|||
|
|||
Add Subtotal functions using cell references
Hello Sean,
With both my original and your modification, I am only able to reference cells A4:A11 before the formula gets too long for Excel, which still leaves out A12:A14. Thank you for your response. If you or anybody else have any other ideas, please post! Thanks, Jeff -- From there to here, from here to there, funny things are everywhere. "Sean Timmons" wrote: =SUMPRODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),(--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$4)+--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$5)+--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$6))) Shrinks it a bit.... "JeffB" wrote: Hello, I have the following formula that I would like to simplify/improve because when it is complete, it is too long for Excel 2003 I am using: =(SUMPRODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$4)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$5)))+(SUMPR ODUCT(--(INDIRECT("'"&$C4&"'!$I$3:$I$1000")=E$2),--(INDIRECT("'"&$C4&"'!$F$3:$F$1000")=$A$6))) ...etc...etc... This formula interprets "raw data" on monthly worksheets with sheet names in the range C4:C15. The A4:A14 range contains collection center codes under which I am trying to sum for the master collection center. All QA codes (I3:I1000 range on "raw data" sheet) are referenced in $E$2:$AI$2 column headers. I have seached the discussions and have not seen any other solutions. Your help and wisdom are appreciated! -- From there to here, from here to there, funny things are everywhere |
Thread Tools | |
Display Modes | |
|
|