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

1. ## 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!  Register To Reply

2. ## 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.  Register To Reply

3. ## 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. Originally Posted by Tony Valko 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.  Register To Reply

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

Can we use sumifs across worksheets  Register To Reply

5. ## Re: SUM(IF()) Array with AND Bollean Multiple Criteria Originally Posted by mahju 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.  Register To Reply

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