Sumproduct and indirect syntax

[AlanY]

I've this sumproduct function that works;

Please Login or Register  to view this content.

with H60 = MD

when I replaced the (MD_1=L60) with (INDIRECT(H60&"_1")=L60) it gives #REF! error.

what have I done wrong please?

[Pete_UK]

Presumably MD_1 and MD_2 are named ranges?

Pete

[AlanY]

yes, they are

[Pete_UK]

Check that you don't have a space at the end of MD in H60.

Hope this helps.

Pete

[AlanY]

no, I even replaced MD with MD_1 in H60, and (INDIRECT(H60)=L60) gives the same error!

[Pete_UK]

Well, without seeing your workbook I can't think of anything else. Do you want to attach it?

Pete

[AlanY]

well, it's over 30M atm.
see if I can trim it down.

[XOR LX]

Having reconstructed your scenario, with MD in cell H60, these two formulas:

=SUMPRODUCT(((MD_1=L60)+(MD_1=M60))*((MD_2=L60)+(MD_2=M60)))

=SUMPRODUCT((((INDIRECT(H60&"_1")=L60)+(INDIRECT(H60&"_1")=M60))*((MD_2=L60)+(MD_2=M60))))

give precisely the same result.

Regards

[AlanY]

on the attached file, formula in U57 to U60 works, but T57 to T60 with indirect that doesn't.

any suggestions would be very much appreciated.

[XOR LX]

I see.

This is an unfortunate result of Excel not being able to pass a range which is defined dynamically (which yours are) to INDIRECT. If you change the definitions of MD_1 and MD_2 to static references, e.g.:

=MD!$H$4:$H$121

and

=MD!$I$4:$I$121

then the INDIRECT formulas will work fine.

Obviously you do not want to lose this dynamism, though. The workaround I know utilizes an old macro-enabled function within Name Manager, called EVALUATE (I presume from your file that you are in any case conversant with VBA).

I would define two further names in Name Manager, making sure that you do so whilst the active cell in the worksheet is somewhere in row 57 (formulas entered here obey the usual rules re relative/absolute referencing):

Arry1 as:

=IF(NOW(),EVALUATE(Data!$H57&"_1"))

Arry2 as:

=IF(NOW(),EVALUATE(Data!$H57&"_2"))

(The initial clause is not strictly necessary, and is also volatile, though the below formula will not automatically recalculate without this volatile addition, even in Automatic Calculation mode. You can remove this clause, though be aware that you will then need to manually force recalculation of these formulas in order to see updated results.)

The formula in T57 is then:

=SUMPRODUCT(((Arry1=L57)+(Arry1=M57))*((Arry2=L57)+(Arry2=M57)))

Regards

[AlanY]

many thanks for your expertise and time.
i think i stick with the non-dynamic ranges, no big deal.

thanks again.

[XOR LX]

Ok. Sensible choice!

And you're welcome!

Regards