#Value with SUMIFS referencing a closed workbook

[jb2_86_uk]

Hi all,

I am attempting to clean-up one of my dashboards by linking it directly to the source data rather than via an Access database. I have hit a problem with #Value errors from a SUMIFS formula. I have found from reading many threads that this function is not compatible with closed workbooks, and according to MS (http://support.microsoft.com/kb/260415) the solution is to replace SUMIF with SUM(IF( array formula, or in the case of a SUMIFS: "Use a logical AND or OR to replace the SUMIFS or COUNTIFS function". Maybe I am just being a bit dim, but I cannot work out how to incorporate the AND into my SUM(IF( formula.

Please can anyone assist with converting my formula?

This is the formula I am trying to recreate:
=SUMIFS([Source]Sheet!'!$A:$A,[Source]Sheet'!$D:$D,$C$3,[Source]Sheet!$L:$L,C$19)
- [Source]A:A are numbers, the rest of the fields/columns referenced are strings

Any help is appreciated!

John

 

[T. Valko]

One way is to use the SUMPRODUCT function.

Have the target file open and use your mouse to point to the ranges and Excel will put the path in for you.

Also, you should avoid using entire columns as range references with the SUMPRODUCT function.

The formula would look something like this...

=SUMPRODUCT(--([Source]Sheet'!$D2:$D100=$C$3),--([Source]Sheet!$L2:$L100=C$19),[Source]Sheet!'!$A2:$A100)

 

[Aladin Akyurek]

=SUMIFS([Source]Sheet!'!$A:$A,[Source]Sheet'!$D:$D,$C$3,[Source]Sheet!$L:$L,C$19)


>>


=SUM(IF([Source]Sheet'!$D$2:$D$1000=$C$3,IF([Source]Sheet!$L$2:$L$1000=C$19,[Source]Sheet!'!$A$2:$A$1000)))

which must be confirmed with control+shift+enter, not just enter.

Done with SUMPRODUCT, we would get:

=SUMPRODUCT([Source]Sheet!'!$A$2:$A$1000,--([Source]Sheet'!$D$2:$D$1000=$C$3),--([Source]Sheet!$L$2:$L$1000=C$19))

which is also an array-processing formula. It needs just enter for confirmation.

 

[jb2_86_uk]

Fantastic! Thank you both for your quick responses.

Regards
John