Forum Discussion
Not sure how to reference array with end row
Have an array I want to reference on Page 1 (where the end row will grow) in a formula on Page 2. Currently this is what i have and it works but I have to manually edit the end row value to include ...
=SUMPRODUCT((INDIRECT("'BP Readings'!C47:C"&MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C)))))*(INDIRECT("'BP Readings'!B47:B"&MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C))))>=A9)*(INDIRECT("'BP Readings'!B47:B"&MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C))))<B9))/SUMPRODUCT((INDIRECT("'BP Readings'!B47:B"&MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C))))>=A9)*(INDIRECT("'BP Readings'!B47:B"&MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C))))<B9))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.