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.

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

    Here is my current frmla

    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


    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

  • Re: Indirect Function Inside Sumproduct Formula




  • 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.


  • 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.



  • 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?


  • 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!


  • Re: Indirect Function Inside Sumproduct Formula


    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.

    Be sure to give your thread a title that accuratly describes your needs.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!