Or, here's a non-array normally entered version but it's longer:

=INT(IF(RPT.REGION="",SUMIF('BGT-YTD'!$AG$2:$AG$438,D10,'BGT-YTD'!AO2:AO438),SUMPRODUCT(--('BGT-YTD'!$AE$2:$AE$438=RPT.REGION),--('BGT-YTD'!AG2:AG438=D10),'BGT-YTD'!AO2:AO438)))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this...

if RPT.REGION is blank or has ***

To help keep things simple just leave RPT.REGION empty. Don't use either
blank or ***.

Array entered** :

=INT(SUM((IF(RPT.REGION="",ROW('BGT-YTD'!$AE$2:$AE$438)0,'BGT-YTD'!$AE$2:$AE$438=RPT.REGION))*('BGT-YTD'!$AG$2:$AG$438=D10)*('BGT-YTD'!$AO$2:$AO$438)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.


--
Biff
Microsoft Excel MVP


"Forgone" wrote in message
...
Is there a way to have an array formula skip a segement if a field is
blank?

For example:

I have a cell named "RPT.REGION"

The formula I'm using is....

=TRUNC(SUM(('BGT-YTD'!$AE$2:$AE$438=RPT.REGION)*('BGT-YTD'!$AG$2:$AG
$438=SLWA!D10)*('BGT-YTD'!$AO$2:$AO$438)),0)

I'm wondering, if at all possible, if RPT.REGION is blank or has ***
then it carries on with the rest of the calculation.

EG: If RPT.REGION = "" then only do SUM('BGT-YTD'!$AG$2:$AG$438=SLWA!
D10)*('BGT-YTD'!$AO$2:$AO$438) otherwise do the lot....

I just tried to do

SUM(('BGT-YTD'!$AE$2:$AE$438=RPT.REGION)+('BGT-YTD'!$AG$2:$AG$438=SLWA!
D10)*('BGT-YTD'!$AO$2:$AO$438)),0)

but the + didn't give me the results I wanted.