paddysheeran
04-30-2012, 03:00 AM
Hi All,
I have a workbook with a number of Sumproduct and SUM Array forumulas. My Workbook is increasing in size and I need to keep this under control to avoid any problems with emailing it out.
My question is can I create a number of User Defined functions for the following formulas to reduce the szie of the file?
{=SUM((Incidents[Service]="WAN")*
(Incidents[SEVERITY]="P1")*
(Incidents[sla_attributable]="Yes")*
(Incidents[Site Class]="DC")*
(Incidents[TOTAL_OUTAGE_TIME__SECS_]))}
and
=SUMPRODUCT(
--(Incidents[SEVERITY]="P1"),
--(Incidents[sla_attributable]="Yes"),
--(Incidents[Site Class]="DC"),
--(Incidents[Service]="WAN"))
and
=IF(ISERROR(VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0)),"Check Matrix",VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0))
thanks.
Paddy.
I have a workbook with a number of Sumproduct and SUM Array forumulas. My Workbook is increasing in size and I need to keep this under control to avoid any problems with emailing it out.
My question is can I create a number of User Defined functions for the following formulas to reduce the szie of the file?
{=SUM((Incidents[Service]="WAN")*
(Incidents[SEVERITY]="P1")*
(Incidents[sla_attributable]="Yes")*
(Incidents[Site Class]="DC")*
(Incidents[TOTAL_OUTAGE_TIME__SECS_]))}
and
=SUMPRODUCT(
--(Incidents[SEVERITY]="P1"),
--(Incidents[sla_attributable]="Yes"),
--(Incidents[Site Class]="DC"),
--(Incidents[Service]="WAN"))
and
=IF(ISERROR(VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0)),"Check Matrix",VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0))
thanks.
Paddy.