Thread: Indirect formula combined with {} formula

Hi All,

Fisrt of all, I appologise for not knowing the name of the second formula that I want to combine, I'll leave an example instead.

=SUM((Pep!H10:H12=Summary!E1)*(Pep!I10:I12))

Now, the issue:

I'm trying to combine the indirect formula with this one, trying to pick data from another sheet by doing the following:

=SUM(INDIRECT(E2&"!"&"$h$10"&":"&"$h$12"&"="&$E$1&")"&"*"&"("&E2&"!"&"$i$10"&":"&"$i$12"))

E2 = PEP
E1 = XYZ

In the formula without the indirect I would pick the XYZ in column "H" and sum the amounts of the comumn "I".

Excel accepts the formula but its giving me a REF

Could anyone help me out on this one?

Thanks,
H

This isn't exactly what you want, but I think it's closer.

=SUMPRODUCT( (INDIRECT(E2 & "!" & $H$10 & ":" & $H$12) = INDIRECT($E$1) ) * INDIRECT(E2 & "!" & $I$10 & ":" & $I$12))

Display the Auditing toolbar and watch the formula evaluate.

Thank you very much.

It was indeed close. The formula worked like this:

=SUMPRODUCT((INDIRECT(E2&"!"&"$H$10"&":"&"$H$12")=($E$1))*INDIRECT(E2&"!"&"$I$10"&":"&"$I$12"))

Regards,
H