+ Reply to Thread
Results 1 to 8 of 8

Why doesn't this array function work?

  1. #1
    Forum Contributor
    Join Date
    05-30-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2013
    Posts
    107

    Why doesn't this array function work?

    I have the following table:

    Please Login or Register  to view this content.
    The following formula returns 13 as I expect it to.
    Please Login or Register  to view this content.
    The following formula returns 0 when I expect it to return 11. Why doesn't it return 11?
    Please Login or Register  to view this content.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Why doesn't this array function work?

    Did you use CTRL-SHIFT-ENTER to commit the formula, instead of the usual ENTER ?

    Hope this helps.

    Pete

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Why doesn't this array function work?

    =SUMPRODUCT((A2:A7="Olof")*(B2:B7="Ice Cream")*(C2:C7))

    wORKS FOR YOU?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Why doesn't this array function work?

    Actually, you can't use AND in that way with array formulae - you need to do it like this:

    =SUM(IF((A2:A7="Olof")*(B2:B7="Ice Cream");C2:C7;0))

    Here the * is equivalent to AND.

    Use CSE to commit.

    Hope this helps.

    Pete

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Why doesn't this array function work?

    Hi OldManExcellor,

    Instead of AND function, use logic as (A2:A7="Olof")*(B2:B7="Ice Cream")


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Why doesn't this array function work?

    Alternatively, use the SUMIFS function that allows for more than one criteria to be used to determine what is summed.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Why doesn't this array function work?

    SUMIFS is not available in XL 2003.

    Pete

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Why doesn't this array function work?

    Quote Originally Posted by newdoverman View Post
    Alternatively, use the SUMIFS function that allows for more than one criteria to be used to determine what is summed.
    .....
    The OP has 2003 according to his/her profile SUMIFS() is for 2007 and above.

    [EDIT].
    You beat me to it Pete!
    Last edited by Marcol; 05-24-2013 at 10:06 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ 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