Average, mins, and maxes for criteria A *OR* B

Hello everyone,

I am totally new here and still kinda fresh when it comes to excel formulas. I am in a bit of a bind though, I hope i am explaining this to a very understandable point. I am trying to find a formula that will average, min, and max a set of numbers based on 2 different criteria possibilities. This is in an attempt to combine two templates used within my company to minimize confusion. Hopefully this image provides enough context. The 'Status' column is usually filled with a variety of text but the common terms used to separate them are 'High/Low' and 'Side A/Side B' but i noticed my formula only seems to work when both criteria are met somewhere within the 'Status' column. HALP!.JPG

the only way I can get a return in E:26 is if i change one of the 'low' boxes to 'side a' which tells me both criteria have to be met in order to average. halp 2.JPG
I need this formula to average low OR side a and the same for high OR side b since they will never be included on the same sheet aside from this trial run.

Also, i am having something kind of similar yet very abnormal happening with the MINS and MAXES. At this point, I just got desperate and sort of copied a bit of a successful formula I found earlier and just went with what I could.halp 3.JPG

halp 4.JPG

i believe this is the same problem. The conditional criteria calls for both 'side b' AND 'high' to be present somewhere in the 'Status' column. Again, this will never occur aside from my tests on the formula. I Hope i explained enough of this, if you want to meet with me over teamviewer, that will be great too. I am just at wit's end and have no idea where to go from here. please and thanks to all who give it a shot

Makes sense. Sorry about that. I just shared the file, hopefully this will work.

YES! That worked! oh my god thank you, i would've never figured that out(writing the formula like an equation)

Please, take your time. Im gonna start from scratch on the MIN & MAX formulas again unless someone steps in.

@ASmith01.... Although you might special-case one formula or another, the following paradigm works for AVERAGE, MIN, MAX and most other functions with minimal change.

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formulas (see the 'Data (2)' worksheet in the attached Excel file):

Please Login or Register  to view this content.

The last example, which could be applied to E26 and E29 as well, demonstrates a general form the works for any number of alternative substrings.

The use of absolute references like $E$3:$E$23 allows us to array-enter one formula (E29), copy-and-paste into other cells, change AVERAGE to MIN, MAX etc or $I$3:$I$23 to another range,then re-array-enter the formula. I find that minimizes typing errors.

The use of IFERROR covers the case when no row matches one of the substrings. Change the null string ("") to whatever you want (e.g. zero).

Absolutely amazing! The first formula you provided for the MINs in E30 is what worked for me. Jeez, I may have spent around 10 hours on this entire sheet and you guys just saved me another week or so learning Excel! This inspires me, for now, I am gracious! Im gonna spend a little more time on this just adding in small features here & there to streamline this process for my team but the big part is done. THANK YOU SOOOO MUCH!