Converting SUMIFS to SUMPRODUCT

[fsueddie21]

Hello -
HI used to SUMIFS statement to read from closed workbooks to find out this doesn't work. I'm having trouble converting the SUMIFS statement to SUMPRODUCT. I'd very much appreciate it someone would help me re-write to a SUMPRODUCT.

=SUMIFS('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$U:$U, '[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!L:L, $G$4,'[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$B:$B,$D$12,'[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$I:$I,">"&$B13-1, '[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$I:$I,"<"&$C13+1, '[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!X:X,">="&$B$7, '[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!X:X,"<="&$C$7)

Thank you very much. Happy to post sample workbooks if needed. First time mr.excel user!
Cheers,

FSUeddie21

 

[RoryA]

Welcome to the forum.

First off, you need to restrict the range you look at so that it is not entire columns. So let's say we use 1000 rows, your formula would become:

=SUMPRODUCT('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$U$2:$U$1000, ('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!L$2:L$1000=$G$4)*('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$B$2:$B$1000=$D$12)*('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$I$2:$I$1000>$B13-1)*('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!$I$2:$I$1000<$C13+1)*('[State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!X$2:X$1000>=$B$7)*([State Funds Financial Management Report_Final.xlsx]Infoporte Transactions'!X$2:X$1000<=$C$7))

I've assumed that row 1 is headers and can be ignored.

 

[fsueddie21]

Thank you so much RoryA. It worked like a charm! Much appreciated.

 

[RoryA]

Glad to help.