Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Array Formula

  1. #1
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there any way to have two conditions in an array formula (sorry if this is easy - bit thick today)
    I typed in the formula below (which works
    with just the one condition) but it doesn't seem to work when I add the AND bit...
    can anyone help - I want the sum of array ak2:ak63707 where conditions in both col a & col b are met....

    (this is formula that dont work)
    =SUM(IF(AND('Jan 2002 Data'!$B$2:$B$63707=$C21,'Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707))

    cheers,

    Will

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 05:52, WillR wrote:
    Is there any way to have two conditions in an array formula (sorry if this is easy - bit thick today)
    I typed in the formula below (which works
    with just the one condition) but it doesn't seem to work when I add the AND bit...
    can anyone help - I want the sum of array ak2:ak63707 where conditions in both col a & col b are met....

    (this is formula that dont work)
    =SUM(IF(AND('Jan 2002 Data'!$B$2:$B$63707=$C21,'Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707))

    cheers,

    Will
    Not tested:

    =SUMPRODUCT(('Jan 2002 Data'!$B$2:$B$63707=$C21)*('Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707)

    you don't need to array enter this
    "Have a good time......all the time"
    Ian Mac

  3. #3
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers....worked a treat.

    Will.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,075
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-16 06:21, WillR wrote:
    Cheers....worked a treat.

    Will.
    Are your range Data'!$B$2:$B$63707 is really filled up with data?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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