# Using AND function within an array formula

1. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

2. ## RE: Using AND function within an array formula

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
>

3. ## RE: Using AND function within an array formula

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

4. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

5. ## RE: Using AND function within an array formula

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
>

6. ## RE: Using AND function within an array formula

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

7. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

8. ## RE: Using AND function within an array formula

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
>

9. ## RE: Using AND function within an array formula

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

10. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

11. ## RE: Using AND function within an array formula

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
>

12. ## RE: Using AND function within an array formula

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

13. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

14. ## RE: Using AND function within an array formula

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
>

15. ## RE: Using AND function within an array formula

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

16. ## Using AND function within an array formula

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

17. ## Re: 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" <forum@OfficeKB.com> wrote in message
news:52337B8618AC0@OfficeKB.com...
>
> 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

18. ## RE: Using AND function within an array formula

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
>

19. ## RE: Using AND function within an array formula

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1