+ Reply to Thread
Results 1 to 5 of 5

SUM(IF()) Array with AND Bollean Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    SUM(IF()) Array with AND Bollean Multiple Criteria

    Hello,

    I am trying to sum but to a closed external file.

    I know that I can use SUM(IF()) Array formula.

    I am wondering how do I use SUM(IF()) Array Formula with AND multiple criteria?

    In the past, the formula below works

    Please Login or Register  to view this content.
    But the above formula is only having 1 criteria.

    What if I want multiple criteria and that all criteria needs to meet.

    Thanks!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUM(IF()) Array with AND Bollean Multiple Criteria

    Try something like this...

    =SUM(IF(('[externalwb.xlsx]Sheet1'!$A$1:$A$100=Z1)*('[externalwb.xlsx]Sheet1'!$B$1:$B$100=Z2),'[externalwb.xlsx]Sheet1'!$O$1:$O$100))

    Still array entered.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: SUM(IF()) Array with AND Bollean Multiple Criteria

    Interesting...I tried that and doesn't work.

    Let me try again and get back to you.

    Quote Originally Posted by Tony Valko View Post
    Try something like this...

    =SUM(IF(('[externalwb.xlsx]Sheet1'!$A$1:$A$100=Z1)*('[externalwb.xlsx]Sheet1'!$B$1:$B$100=Z2),'[externalwb.xlsx]Sheet1'!$O$1:$O$100))

    Still array entered.

  4. #4
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Re: SUM(IF()) Array with AND Bollean Multiple Criteria

    Can we use sumifs across worksheets
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUM(IF()) Array with AND Bollean Multiple Criteria

    Quote Originally Posted by mahju View Post
    Can we use sumifs across worksheets
    They're referencing an external workbook. SUMIF(S) only works if the external workbook is open.

    When referencing an external workbook it's always better to use either a SUM/IF array or the SUMPRODUCT function. Both will work when the source file is closed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX Array Formula with multiple criteria in multiple columns, is it possible?
    By jparve3283 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-11-2016, 05:51 PM
  2. Countifs multiple criteria array
    By lamdl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2014, 09:18 PM
  3. Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's
    By akaushik25 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 08:58 AM
  4. AutoFilter Multiple Criteria Array
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2013, 04:33 AM
  5. [SOLVED] Excel 2007 : Multiple criteria without using an array formula
    By picasso194 in forum Excel General
    Replies: 7
    Last Post: 05-20-2012, 12:33 PM
  6. Array Formula with multiple criteria
    By amotto11 in forum Excel General
    Replies: 2
    Last Post: 05-14-2012, 03:22 PM
  7. Multiple Criteria Array ?
    By Holtster in forum Excel General
    Replies: 2
    Last Post: 09-07-2007, 04:59 AM

Tags for this Thread

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