I am trying to do something similar to a Averageif with max and min.
Current formula :
=IF(J15<0,MAX('CS+MS'!C:C),MIN('CS+MS'!C:C)) When I'm looking in CS+MS Sheet I want to do something like averageif where I can search column B for a word then return column C.
Is this possble?
If you have OFFICE 365:
=IF(J15<0,MAXIFS('CS+MS'!C:C,'CS+MS'!B:B,"SomeString"),MINIFS('CS+MS'!C:C,'CS+MS'!B:B,"SomeString")) IF You have Excel 2010 or later:
=IF(J15<0,AGGREGATE(14,7,'CS+MS'!C:C/('CS+MS'!B:B="SomeString"),1),AGGREGATE(15,7,'CS+MS'!C:C/('CS+MS'!B:B="SomeString"),1)) If neither use this array formula:
=IF(J15<0,MAX(IF('CS+MS'!B:B="SomeString",'CS+MS'!C:C)),MIN(IF('CS+MS'!B:B="SomeString",'CS+MS'!C:C))) Being an array formula one must use Ctrl-Shift-Enter instead of Enter when exiting edit mode.
14 is Large and 15 is Small.For Microsoft 365 users can use below formula at one go.
=LET(x,UNIQUE(A1:A6),y,MINIFS(B1:B6,A1:A6,x),CHOOSE({1,2},x,y)) 
