SUMIF combine with different file return VALUE error

One option is to use SUMPRODUCT (or an array formula) instead of SUMIF - these will work even when the source file is closed

What's your SUMIF formula?

Originally Posted by greg151091

=SUMIF('D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[TAMANSARI.xls]PER TOKO'!$B$1:$B$65536;$B:$B;'D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[TAMANSARI.xls]PER TOKO'!C$1:C$65536)+SUMIF('D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[GOTONGROYONG.xls]PER TOKO'!$B$1:$B$65536;$B:$B;'D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[GOTONGROYONG.xls]PER TOKO'!C$1:C$65536)

Where you have the whole column B as the criteria you are presumably just using the value from that row, so assuming first formula is in row 2 the SUMPRODUCT equivalent would be as follows:

=SUMPRODUCT(('D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[TAMANSARI.xls]PER TOKO'!$B$1:$B$65536=B2)+0;'D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[TAMANSARI.xls]PER TOKO'!C$1:C$65536)+SUMPRODUCT(('D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[GOTONGROYONG.xls]PER TOKO'!$B$1:$B$65536=B2);'D:\Document\mas joko\_KUMPULAN\CV_greg\CAITHLYN\[GOTONGROYONG.xls]PER TOKO'!C$1:C$65536)

SUMPRODUCT will be slower than SUMIF - If you don't really need to reference 65536 rows then it would be better to shorten the range