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.

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.