On Feb 17, 11:58*am, "T. Valko" wrote:
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.- Hide quoted text -

- Show quoted text -

I've never seen this before -- ROW('BGT-YTD'!$AE$2:$AE$438)0 <-- how
does it work?