Forum Discussion

Chris_Premo's avatar
Chris_Premo
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 the new data.  So what I want to do is rewrite the formula to automatically look to a field on Page 2 where I calculate the "new row" value [using this formula =MAX(('BP Readings'!C:C<>"")*(ROW('BP Readings'!C:C)))  ].

 

=SUMIFS('BP Readings'!C$47:C$88,'BP Readings'!$B$47:$B$88,">="&$A9,'BP Readings'!$B$47:$B$88,"<"& $B9)/COUNTIFS('BP Readings'!$B$47:$B$88,">="& $A9,'BP Readings'!$B$47:$B$88,"<"&$B9)

excel
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    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
    )
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 08, 2023
    One approach is to table the data in BP Readings and use structured references.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 08, 2023

    Chris_Premo 

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