Dynamic LARGE-ROW function

[adipa]

Hi

I have a formula that works - {=SUMPRODUCT(LARGE(IF(H:H=A27,X:X),ROW(1:930)))}

However, 930 can change and as such I need this to be dynamic but if I replace 930 to 'Z1' as a cell refernce it does not work. Tried few other but that failed to. Maybe I need to use INDEX/MATCH, not sure.

Any help much appreciated.

Thanks.

[tim201110]

=sumproduct(large(if(h:h=a27,x:x),row(a1:index(a:a;z1))))
=sumproduct(large(if(h:h=a27,x:x),row(indirect("1:"&z1))))

[adipa]

Hi Tim

Thanks, the second one worked.

Is there any other way to do this as the file has gone extremely slow after using LARGE() formula?

[JeteMc]

Considering regional settings you may need to modify Tim's first formula to read:

Please Login or Register  to view this content.

The formula might be a bit faster if LARGE is replaced with AGGREGATE(14,6... as in:

Please Login or Register  to view this content.

We might be better able to help if we could see a desensitized example of what you are attempting to accomplish. To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[tim201110]

=sumproduct((x:x>=large(if(h:h=a27,x:x),z1))*(h:h=a27))
full column in array formula is not wise.

[FDibbins]

I would advise against using full-column references in both SP and array formulas, it could slow your file down. Rather, use 2-3 times the range you think you will need.