Wildcard search

F

Forgone

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.
 
T

T. Valko

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.
 
T

T. Valko

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)))
 
F

Forgone

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








- 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))}
 
F

Forgone

Try it like this...


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











- Show quoted text -

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

T. Valko

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.REGION))*(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)=1>0=T
ROW(A2)=2>0=T
ROW(A3)=3>0=T
ROW(A4)=4>0=T
ROW(A5)=5>0=T

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

=SUM((IF(RPT.REGION="",ROW(A1:A5)>0,A1:A5=RPT.REGION))*(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


Try it like this...


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











- Show quoted text -

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top