Need to Convert SUMIFS Formula so that closed Workbook can be referenced

[ExcelUser678]

Hello,

I'm trying to convert the formula below so that a closed workbook can be referenced on a workbook to be posted within Teams. Any assistance would be appreciated!

=SUMIFS('[DATA EXPORTS.xlsx]HOURS EXPORT - TASK SUMMARY'!$E:$E,'[DATA EXPORTS.xlsx]HOURS EXPORT - TASK SUMMARY'!$J:$J,S5)

Also asked here Need to Convert SUMIFS Formula so that closed Workbook can be referenced

 

[Dave Patton]

Suggestion
1. limit the ranges to a reasonable number
2. try SumProduct or Sum

I show two examples of the syntax. Review and try with your data; covert the filenames and ranges etc.

to your information.

SumProduct2022a.xlsm
	A	B	C	D
1
2
3			ABC	500
4			CCC	2000
5
6			ABC	500
7			CCC	2000
8
9
7d

Cell Formulas
Range	Formula
D3:D4	D3	=SUMPRODUCT(--('D:\Excel14\[Data21.xlsm]Co_List'!$E$5:$E$50=C3),'D:\Excel14\[Data21.xlsm]Co_List'!$J$5:$J$50)
D6:D7	D6	=SUM(('D:\Excel14\[Data21.xlsm]Co_List'!$E$5:$E$50=C6)*'D:\Excel14\[Data21.xlsm]Co_List'!$J$5:$J$50)

SumProduct2022a.xlsm
	A	B	C	D
1
2
3			ABC	500
4			CCC	2000
5
6			ABC	500
7			CCC	2000
8
7d

Cell Formulas
Range	Formula
D3:D4	D3	=SUMPRODUCT(--([Data21.xlsm]Co_List!$E$5:$E$50=C3),[Data21.xlsm]Co_List!$J$5:$J$50)
D6:D7	D6	=SUM(([Data21.xlsm]Co_List!$E$5:$E$50=C6)*[Data21.xlsm]Co_List!$J$5:$J$50)

 

[ExcelUser678]

I'm still getting a value error and I think it has to do with the fact that the data in Column J in my source data returns some #N/A values. Any suggestions for workarounds to remove the #N/A values - correcting the error causing it is not an option...

 

[Dave Patton]

What syntax did you use with your SumProduct formula?

You may secure additional help if you post an extract of your sheet (say 10 rows of relevant data) and show the expected results.
N.B. You can post an extract with the forum's tool that is named XL2BB.

"Any suggestions for workarounds to remove the #N/A values - correcting the error causing it is not an option..."
But you want results from a formula?

 

[ExcelUser678]

I got everything working now! I created an IFERROR formula to return x if my XLOOKUP returned N/A and then utilized the SUM formula to curcumvent the closed workbook! Thank you for all your help!

 

[Alex Blakenburg]

If you are using XLookup it has IfError built in. Just put your replacement value in the 4th position (if_not_found)
=xlookup(lookup_value, lookup_array, return_array, if_not_found, match_mode,s earch_mode)

 

[Alex Blakenburg]

On a more general note, the new Filter function will do closed workbooks and is pretty flexible.