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
|