 # Indirect Function Inside Sumproduct Formula

• Hi all -

I am attempting to build an interactive user-app to let the user choose a table name and update P&L values based on selection.

Assumptions
Start: Row 2
End: Row 2831 (Some tables less, this is max size table)

Here is my current frmla
=SUMPRODUCT((INDIRECT(\$B\$1&"!"&\$A\$2:\$A\$2831)=B\$8)*(INDIRECT(\$B\$1&"!"&\$C\$2:\$C\$2831)=\$A9)*(INDIRECT(\$B\$1&"!"&\$D\$2:\$D\$2831)))

Where:
B\$8 is comparing the Unit number
\$A9 is comparing descriptive label
\$D\$2:\$D\$2831 is range of values to be summed if 2 conditions are met

This frmla is currently returning a #REF! error

So then I tried re-writing the Indirect arg's by placing the COl/Row Ref's in cells

=SUMPRODUCT((INDIRECT(\$B\$1&"!"&\$B\$3&\$B\$4)=B\$9)*(INDIRECT(\$B\$1&"!"&\$C\$3&\$C\$4)=\$A10)*(INDIRECT(\$B\$1&"!"&\$D\$3&\$D\$4)))

This is also returning a #REF! error

I attached a small sample file
I had to trim all data sheets out but 1
I had to trim out all data but 2 units
Should be enough to test with though

Thanks much
-marc

## Files

• Re: Indirect Function Inside Sumproduct Formula

=sumproduct((indirect(\$b\$1&"!a2:a2831")=b\$8)*
(indirect(\$b\$1&"!c2:c2831")=\$a9)*
(indirect(\$b\$1&"!d2:d2831")))

HTH

Bob

• Re: Indirect Function Inside Sumproduct Formula

Hi marc,

File attached. All that is missing from your formula is ":" between the row and columns.

Bill

## Files

• Re: Indirect Function Inside Sumproduct Formula

Thanks for the help guys.
Both solutions worked equally well
But I like the latter a little better as the end range could grow in the future

I guess I need to loop through each data sheet and get the end row nad use the maximum value in the frmla.

Thanks!
-marc

• Re: Indirect Function Inside Sumproduct Formula

If you used dynamic named ranges, it would cater for growth, and you would have no need for the appalling (inefficient) INDIRECT function.

HTH

Bob

• Re: Indirect Function Inside Sumproduct Formula

Thanks Bob -

I will add the dynamic named ranges for the criteria and the range to sum
But I believe I still need to use indirect to allow the user to choose which table (sheet) is to be presented.

Unless there is a way around this?

Thanks
-marc

• Re: Indirect Function Inside Sumproduct Formula

Coming back to that solution. I've been trying to apply this with slightly different requirements. Data source is placed in a different file, therefore Indirect function is also a bit more complecated. I wrote sth like below:

=SUMPRODUCT((INDIRECT("'["&\$R25&\$G\$3&\$O\$3&\$S25&"]"&\$T25&"'!"&"B"&\$U25&":B"&\$V25)="INDUSTRIAL COATINGS")*(INDIRECT("'["&\$R25&\$G\$3&\$O\$3&\$S25&"]"&\$T25&"'!"&"C"&\$U25&":C"&\$V25)="DAMAGED MATERIAL")*(INDIRECT("'["&\$R25&\$G\$3&\$O\$3&\$S25&"]"&\$T25&"'!"&"E"&\$U25&":E"&\$V25)))

So from external file in column B it searched the line with text INDUSTRIAL COATINGS and from column C text "DAMAGED MATERIAL" and then I want to get from column E the number - in that case it's 1. But instead of that it returns an error #VALUE!

[ATTACH=CONFIG]54493[/ATTACH]

## Images

• Re: Indirect Function Inside Sumproduct Formula

Przemo,

Welcome to Ozgrid.
Please do not post your questions in threads started by others - always start a new thread for your questions and if it help provide some context or clarity you may include a link to this, or any other, thread.