+ Reply to Thread
Results 1 to 19 of 19

Using AND function within an array formula

  1. #1
    Bob Phillips
    Guest

    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. #2
    bj
    Guest

    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. #3
    Andrew L via OfficeKB.com
    Guest

    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. #4
    Bob Phillips
    Guest

    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. #5
    bj
    Guest

    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. #6
    Andrew L via OfficeKB.com
    Guest

    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. #7
    Bob Phillips
    Guest

    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. #8
    bj
    Guest

    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. #9
    Andrew L via OfficeKB.com
    Guest

    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. #10
    Bob Phillips
    Guest

    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. #11
    bj
    Guest

    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. #12
    Andrew L via OfficeKB.com
    Guest

    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. #13
    Bob Phillips
    Guest

    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. #14
    bj
    Guest

    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. #15
    Andrew L via OfficeKB.com
    Guest

    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. #16
    Andrew L via OfficeKB.com
    Guest

    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. #17
    Bob Phillips
    Guest

    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. #18
    bj
    Guest

    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. #19
    Andrew L via OfficeKB.com
    Guest

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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