Group and Outline

Hello, I am working with a large data set in Excel, where certain rows are grouped and collapsed. I was wondering if there is a way to set up formulas that only reference visible data. For example, if I want to calculate the max value from an array in column A, is there any way to set up the function so it will ignore data that are in grouped & collapsed rows? Thanks!

Works beautifully. Thank you!