Forum Discussion

Copper Contributor

Jun 08, 2023

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 ...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Jun 08, 2023

Chris_Premo

As variant

=SUMIFS(
    'BP Readings'!C$47:INDEX('BP Readings'!$C$47:$C$10000, COUNTA('BP Readings'!$A$47:$A$1000) ),
    'BP Readings'!$B$47:INDEX('BP Readings'!$B$47:$B$10000, COUNTA('BP Readings'!$A$47:$A$1000) ), ">=" & $A9,
    'BP Readings'!$B$47:INDEX('BP Readings'!$B$47:$B$10000, COUNTA('BP Readings'!$A$47:$A$1000) ), "<" & $B9
) /
    COUNTIFS(
        'BP Readings'!$B$47:INDEX('BP Readings'!$B$47:$B$10000, COUNTA('BP Readings'!$A$47:$A$1000) ), ">=" & $A9,
        'BP Readings'!$B$47:INDEX('BP Readings'!$B$47:$B$10000, COUNTA('BP Readings'!$A$47:$A$1000) ), "<" & $B9
    )