CODEWITHSUNDEEP
excel
CafeGirl90
CafeGirl90

Reputation: 15

How do I convert COUNTIFS formula to SUM(IF to avoid #VALUE due to external links

I have an Excel formula which references several cells in other files, of the format: =COUNTIFS('[FILE1.xlsx]Sheet 1'!$D$2:$D$5000,"A",'[FILE1.xlsx]Sheet 2'!$H$2:$H$5000,"B")

When I open this file, the cell value defaults to #VALUE (if FILE1.xlsx is closed).

Searching Microsoft Support (https://support.microsoft.com/en-us/kb/260415) this is because formulas such as SUMIF, SUMIFS, COUNTIF, COUNTIFS or COUNTBLANK automatically calculate on spreadsheet opening.

Microsoft's workaround is to replace COUNTIF with SUM(IF(Range=Condition,1,0) entered as an array. (There is no example for COUNTIFS.)

My question is: How do I change my formula to be of the format SUM(IF (AND?) ? (I do not want my formula trying to recalculate if I open the spreadsheet with the source file closed.)

I've tried =SUM(IF(AND('[FILE1.xlsx]Sheet 1'!$D$2:$D$5000="A",'[FILE1.xlsx]Sheet 2'!$H$2:$H$5000="B"),1,0)) (as an array)

But this returns 0.

Any thoughts? (Or must I revert to a clunky 3rd column in my source spreadsheet containing the COUNTIF logic so my file can just use SUM instead ...?)

Upvotes: 1

Views: 1896

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

The array equivalent to your COUNTIFS would be:

{=SUM(IF(('[FILE1.xlsx]Sheet 1'!$D$2:$D$5000="A")*('[FILE1.xlsx]Sheet 2'!$H$2:$H$5000="B"),1,0))}

But

=SUMPRODUCT(('[FILE1.xlsx]Sheet 1'!$D$2:$D$5000="A")*('[FILE1.xlsx]Sheet 2'!$H$2:$H$5000="B"))

should also work.

Upvotes: 1

Related Questions