Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Subtotal with two functions | Excel Discussion (Misc queries) | |||
subtotal functions | Excel Worksheet Functions | |||
Subtotal Range of Alphanumeric References | Excel Worksheet Functions | |||
How to use complex no. functions that have cell references? | Excel Worksheet Functions |