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.

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.

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.

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

- Show quoted text -

I've ended up doing something like this (the long way) but I'll have a
play with both suggestions above.

{=IF(RPT.REGION<"",(TRUNC(SUM((('TB-PTD'!$L$2:$L$933=RPT.REGION)*('TB-
PTD'!$N$2:$N$933=$D10)*('TB-PTD'!$T$2:$T$933))),0)),TRUNC(SUM(('TB-
PTD'!$N$2:$N$933=$D10)*('TB-PTD'!$T$2:$T$933)),0))}

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?

Let's use this simplified sample to see how that works...

...........A..........B..........C..........D
1.......W..........X..........5.........W
2........E..........X..........4
3.......W.........Y..........5
4.......W.........X..........3
5........E.........X..........3
10...........................................X

Let's assume cell D1 is the named cell RPT.REGION

RPT.REGION is a variable that determines what criteria to use from the range
A1:A5 in the calculation. If RPT.REGION is empty that means we want to
*exclude* that criteria from the calculation.

So, as is our goal is to sum C1:C5 where A1:A5 = W and B1:B5 = X

Array entered**

=SUM((IF(RPT.REGION="",ROW(A1:A5)0,A1:A5=RPT.REGI ON))*(B1:B5=D10)*(C1:C5))

The result of this formula is 8. Row 1 and row 4 are included in the
calculation.

We use an IF function to determine what cells in A1:A5 to "include" in the
calculation.

If RPT.REGION is empty use: ROW(A1:A5)0
If RPT.REGION is not empty use: A1:A5=RPT.REGION

As is, RPT.REGION is not empty so we use: A1:A5=RPT.REGION.

This returns an array of either TRUE or FALSE:

A1=W=T
A2=W=F
A3=W=T
A4=W=T
A5=W=F

When RPT.REGION is empty we use: ROW(A1:A5)0

This also returns an array of either TRUE or FALSE *but* in this case every
element of the array will be TRUE. ROW(...) returns the row number
referenced in its argument. The row number has to be a number from 1 to the
max number of rows a worksheet can have which is Excel version dependent. So
we test the number retruned by ROW to see if it is 0. This number *must* be
0 so the resulting array will return nothing but TRUEs.

ROW(A1)=10=T
ROW(A2)=20=T
ROW(A3)=30=T
ROW(A4)=40=T
ROW(A5)=50=T

Now, let's see how that applies to the overall formula.

=SUM((IF(RPT.REGION="",ROW(A1:A5)0,A1:A5=RPT.REGI ON))*(B1:B5=D10)*(C1:C5))

The basic process of the formula is simply multiplying 3 arrays together to
arrive at a result. 2 of those arrays return Boolean TRUE or FALSE:

(IF(RPT.REGION="",ROW(A1:A5)0,A1:A5=RPT.REGION))
(B1:B5=D10)

When multiplying Boolean values together the result is either 1 or 0.

T*T=1
T*F=0
F*T=0
F*F=0

...........A..........B..........C..........D
1.......W..........X..........5.........W
2........E..........X..........4
3.......W.........Y..........5
4.......W.........X..........3
5........E.........X..........3
10...........................................X

Based on that sample data the 3 arrays look like this:

T*T*5 = 5
F*T*4 = 0
T*F*5 = 0
T*T*3 = 3
F*T*3 = 0

SUM({5;0;0;3;0}) = 8

When RPT.REGION is empty the 3 arrays look like this:

T*T*5 = 5
T*T*4 = 4
T*F*5 = 0
T*T*3 = 3
T*T*3 = 3

SUM({5;4;0;3;3}) = 15

So, we're using a trick in the formula so that it evaluates every element in
the array A1:A5 as TRUE which causes that array to have no impact on the
calculation or, is in essence *excluded* from the calculation. If the A1:A5
array is excluded this is what the 2 remaining arrays would look like:

T*5 = 5
T*4 = 4
F*5 = 0
T*3 = 3
T*3 = 3

SUM({5;4;0;3;3}) = 15

The calculation is basically reduced to a simple SUMIF(B1:B5,D10,C1:C5).


--
Biff
Microsoft Excel MVP


"Forgone" wrote in message
...
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?