# 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!

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.

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.

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

Can we use sumifs across worksheets

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.

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