Sumproduct)indirect

[Cooki]

Cant get my sumproduct indirect to work

=SUMPRODUCT((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$R$2:$R$100")),--MONTH((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$B$2:$B$100")))=$AC$3)

A6 to Name of tab
B2:B100 is dates
R2:R100 is the values for those dates
AC3 is month i want to search for

Could anyone help please

Rich

 

[Tegg]

Hello!

It looks like there are some formatting issues in the formula, try this:

=SUMPRODUCT((INDIRECT($A$6&"'[Metering Services 3 Productivity 2020 NEW.xlsx]'!$R$2:$R$100")),--MONTH((INDIRECT($A$6&"'[Metering Services 3 Productivity 2020 NEW.xlsx]'!$B$2:$B$100")))=$AC$3)

 

[jkpieterse]

Please note that SUMPRODUCT is a calculation-intensive function which combined with INDIRECT always recalculates and thus may make your model quite slow.

 

[Cooki]

Hello!

It looks like there are some formatting issues in the formula, try this:

=SUMPRODUCT((INDIRECT($A$6&"'[Metering Services 3 Productivity 2020 NEW.xlsx]'!$R$2:$R$100")),--MONTH((INDIRECT($A$6&"'[Metering Services 3 Productivity 2020 NEW.xlsx]'!$B$2:$B$100")))=$AC$3)

Getting a #REF

 

[Cooki]

Please note that SUMPRODUCT is a calculation-intensive function which combined with INDIRECT always recalculates and thus may make your model quite slow.

Should be ok, as there is not much data on there