Sumproduct function with incremental row while dragging

[arijitirf]

Hi!!!

Below formula is working well when the workbook is open but returns a #Ref Error if the reference workbook is closed.

=SUMIF(INDIRECT("'[Stock Register.xlsx]Stock Code "&ROW(A1)&"'!"&"$O$8:$O$500"),"Issued to Sanitary Syndicate",INDIRECT("'[Stock Register.xlsx]Stock Code "&ROW(A1)&"'!"&"$I$8:$I$500"))

I've used Sumproduct instead of sumif function but in this function row increment is not happening while dragging the formula from above row.

=SUMPRODUCT(--('C:\Users\Ferroequinologist\Desktop\[Stock Register.xlsx]Stock Code 1'!$E$3:$E$500="Issued to Sanitary Syndicate"),'C:\Users\Ferroequinologist\Desktop\[Stock Register.xlsx]Stock Code 1'!$C$3:$C$500)

Help required.

 

[DRSteele]

I read the Help article from MS about function INDIRECT; it says "If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF ! error value."

Dragging your SUMPRODUCT formula downwards will not increment the row reference if you have absolute references (i.e., the $). Remove the $.

[And have fun with your train hobby!. I see you are a person interested in studying the iron horse.]