Results 1 to 5 of 5

array formula or sumif or sumproduct

This is a discussion on array formula or sumif or sumproduct within the Excel Questions forums, part of the Question Forums category; Hello In column A I have sales figures. The range is named 'sales'. In column 2 I have postcodes. The ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    776

    Default array formula or sumif or sumproduct

    Hello
    In column A I have sales figures. The range is named 'sales'. In column 2 I have postcodes. The range is named 'post'. I am trying to find a formula which will add all the sales which have a post code of BT5 or BT6 or BT7. I thought it was an array formula but I cant get it to work. The formula tried was:

    SUM((sales)*(OR(postcode="bt5",postcode="bt6",postcode="bt7")))

    I have the curly brackets entered threough 'control-shift-enter'. Is the OR a problem or brackets or should I be using a different formula?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,900

    Default Re: array formula or sumif or sumproduct

    =SUMPRODUCT(--ISNUMBER(MATCH(post,X2:X4,0)),sales)

    where X2:X4 houses the criteria BT5, BT6, and BT7.

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    776

    Default Re: array formula or sumif or sumproduct

    Thank you very much. That works perfectly for what I asked you. The only wee problem I have is that sometimes the sale has no post code and it throws the formulas out. The range 'postcode' is dynamic and will have the same cells in it as sales even if there is no post code.
    Any thoughts

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,900

    Default Re: array formula or sumif or sumproduct

    Quote Originally Posted by methody
    Thank you very much. That works perfectly for what I asked you. The only wee problem I have is that sometimes the sale has no post code and it throws the formulas out. The range 'postcode' is dynamic and will have the same cells in it as sales even if there is no post code.
    Any thoughts
    What does "sometimes the sale has no post code and it throws the formulas out" mean?

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    776

    Default Re: array formula or sumif or sumproduct

    Sorry I had made a mistake with naming my ranges.
    Thnaks for your help

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
  •  


DMCA.com