Formula compaction
 

Please suggest a more compact format for this formula:
=SUMPRODUCT(
(INDIRECT("RawData!B"&$I$32&":B"&$J$32)=105)*
(INDIRECT("RawData!AA"&$I$32&":AA"&$J$32)="M6")*
(INDIRECT("RawData!AG"&$I$32&":AG"&$J$32)="CLOSED" )*
(INDIRECT("RawData!P"&$I$32&":P"&$J$32)="CLOSED")*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)=Calcula tedData!H2)*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)<=Calcula tedData!H3)*
(INDIRECT("RawData!AF"&$I$32&":AF"&$J$32)INDIRECT ("RawData!AD"&$I$32&":AD"&$J$32))*
((ISNUMBER(MATCH(INDIRECT("RawData!E"&$I$32&":E"&$ J$32),Definitions!$B$3:$B$6,0)))))
)

Also, is there a way to alias frequent constructs, like
("RawData!B"&$I$32&":B"&$J$32) and replace with a parametrized syntax, like
€œdata(B)€�, still using a worksheet formula.

Thank you.

Formula compaction
 

Last part of question:
I used Define Names to define: Somewhere --- Refers to "Sheet3!C"
In a cell on Sheet1 I use =INDIRECT(Somewhere&G1)
This returned the value from C1 in Sheet3
I could have used a shorter name but you must avoid names that could be
mistaken for cell references
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"adimar" wrote in message
...
Please suggest a more compact format for this formula:
=SUMPRODUCT(
(INDIRECT("RawData!B"&$I$32&":B"&$J$32)=105)*
(INDIRECT("RawData!AA"&$I$32&":AA"&$J$32)="M6")*
(INDIRECT("RawData!AG"&$I$32&":AG"&$J$32)="CLOSED" )*
(INDIRECT("RawData!P"&$I$32&":P"&$J$32)="CLOSED")*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)=Calcula tedData!H2)*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)<=Calcula tedData!H3)*
(INDIRECT("RawData!AF"&$I$32&":AF"&$J$32)INDIRECT ("RawData!AD"&$I$32&":AD"&$J$32))*
((ISNUMBER(MATCH(INDIRECT("RawData!E"&$I$32&":E"&$ J$32),Definitions!$B$3:$B$6,0)))))
)

Also, is there a way to alias frequent constructs, like
("RawData!B"&$I$32&":B"&$J$32) and replace with a parametrized syntax,
like
"data(B)", still using a worksheet formula.

Thank you.

Formula compaction
 

Here is my suggestion, using defined names:
http://www.freefilehosting.net/download/3bddd

Formula compaction
 

Some feedback: Having cleaned-up and re-organized my application I must tell
you the advice you gave me in this post made a huge difference in efficiency
and readability.

Please let me know if you have other €œbest practices€� you can share.

Thank you!


"Herbert Seidenberg" wrote:

Here is my suggestion, using defined names:
http://www.freefilehosting.net/download/3bddd