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?
