Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement with 2 array formulae
Hi,
I have an array formula, which did what it was meant to. However, i need now to add a combo box to drill down further. What i want to do is, if the combo selection is ALL, then run the array formula that i was using before the combo was added, if it isnt then add the extra to the array formula. This is what it have Previous Formula (ALL): {SUM((Data!$A$2:$A$919=Regional!$D$8)*(Data!$A$2: $A$919<=Regional!$G$8)*(Data!$D$2:$D$919=Regional! $C14))} New Formula (Not ALL) {SUM((Data!$A$2:$A$919=Regional!$D$8)*(Data!$A$2: $A$919<=Regional!$G$8)*(Data!$D$2:$D$919=Regional! $C14)*(Data!$E$2:$E$919=Regional!$N$4))} D8 and G8 are dates, C column is the region, and data is obviously the data sheet. I have tried this, but it doesnt work. {=IF($J$4="All",SUM((Data!$A$2:$A$919=Regional!$D $8)*(Data!$A$2:$A$919<=Regional!$G$8)*(Data!$D$2:$ D$919=Regional!$C14)),SUM((Data!$A$2:$A$919=Regio nal!$D$8)*(Data!$A$2:$A$919<=Regional!$G$8)*(Data! $D$2:$D$919=Regional!$C14)*(Data!$E$2:$E$919=Regio nal!$N$4)))} The 2nd half works, but the All bit doesnt. Thanks in advance Nathan. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement with 2 array formulae
Try this
=SUM((Data!$A$2:$A$919=Regional!$D$8)* (Data!$A$2:$A$919<=Regional!$G$8)* (Data!$D$2:$D$919=Regional!$C14)* IF($J$4="All",Data!$E$2:$E$919=Regional!$N$4,1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nathan_savidge" wrote in message ... Hi, I have an array formula, which did what it was meant to. However, i need now to add a combo box to drill down further. What i want to do is, if the combo selection is ALL, then run the array formula that i was using before the combo was added, if it isnt then add the extra to the array formula. This is what it have Previous Formula (ALL): {SUM((Data!$A$2:$A$919=Regional!$D$8)*(Data!$A$2: $A$919<=Regional!$G$8)*(Data!$D$2:$D$919=Regional! $C14))} New Formula (Not ALL) {SUM((Data!$A$2:$A$919=Regional!$D$8)*(Data!$A$2: $A$919<=Regional!$G$8)*(Data!$D$2:$D$919=Regional! $C14)*(Data!$E$2:$E$919=Regional!$N$4))} D8 and G8 are dates, C column is the region, and data is obviously the data sheet. I have tried this, but it doesnt work. {=IF($J$4="All",SUM((Data!$A$2:$A$919=Regional!$D $8)*(Data!$A$2:$A$919<=Regional!$G$8)*(Data!$D$2:$ D$919=Regional!$C14)),SUM((Data!$A$2:$A$919=Regio nal!$D$8)*(Data!$A$2:$A$919<=Regional!$G$8)*(Data! $D$2:$D$919=Regional!$C14)*(Data!$E$2:$E$919=Regio nal!$N$4)))} The 2nd half works, but the All bit doesnt. Thanks in advance Nathan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using wildcards in Array formulae | Excel Discussion (Misc queries) | |||
Array formulae - problem | Excel Discussion (Misc queries) | |||
How do I reference certain rows within array formulae? | Excel Worksheet Functions | |||
How to concatenate an array of four stings using formulae (not UDF) | Excel Worksheet Functions | |||
OFFSET and array formulae | Excel Discussion (Misc queries) |