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

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

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