Using AND function within an array formula

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

=SUM(IF(($L$2:$L$10000>=E5)*($L$2:$L$10000<=E5+6)*($AL$2:$AL$10000="166389")
*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew L via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND
function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1

I'm still having some problems with the syntax for nesting other functions
within an array formula.

I want to do something like:
=SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
$10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))

The nested MID function works fine, but when I try to put in the AND function
then I have problems.

Suggestions?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

what do you want it to do when it doen't meet the criteria?
try using
....$G$2:$G$10000,0))
I assume you are using the control-shift-enter to enter the equation.



"Andrew L via OfficeKB.com" wrote:

>
> I'm still having some problems with the syntax for nesting other functions
> within an array formula.
>
> I want to do something like:
> =SUM(IF((AND($L$2:$L$10000>=E5,$L$2:$L$10000<=E5+6))*($AL$2:$AL
> $10000="166389")*(MID($A$2:$A$10000,4,1)="2"),$G$2:$G$10000))
>
> The nested MID function works fine, but when I try to put in the AND function
> then I have problems.
>
> Suggestions?
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200508/1
>

Duh! I forgot, * , does mean AND. Brain cramp!

Q: what do you want it to do when it doen't meet the criteria?
A: that part I know. i was just trying to keep the example simple.

Thanks guys! Very much appreciated.

Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1