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

    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


    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]

    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.


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

Participate now!

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

Register Yourself Login