|
Wildcard search
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. |
Wildcard search
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. |
Wildcard search
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. |
Wildcard search
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))} |
Wildcard search
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? |
Wildcard search
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? |
| All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com