I am in need of an alternative of MAXIFS/MINIFS function in Excel.

It is needed because I am not a Office 365 subscriber. As you know MAXIFS/MINIFS functions let you calculate under certain criteria, even the criteria that includes another cell/s. I can do a least amount of calculation using MAX/MIN and IF function, but the criteria/condition must be on the same cell/s. So it's my misfortune that can't come up on a solution to this case.

I want to use 2 conditions/criteria, so I couldn't use the ARRAY formula.

Result:

0

1 Answer

Look into AGGREGATE() as a LARGE or SMALL:

MIN:

=AGGREGATE(15,6,$B$1:$B$20/(($A$1:$A$20=D3)*($C$1:$C$20=E3)),1)

MAX:

=AGGREGATE(14,6,$B$1:$B$20/(($A$1:$A$20=D3)*($C$1:$C$20=E3)),1)

---

You can also use the array form of MIN(IF()) and MAX(IF())

MIN:

=MIN(IF(($A$1:$A$20=D3)*($C$1:$C$20=E3),$B$1:$B$20))

MAX:

=MAX(IF(($A$1:$A$20=D3)*($C$1:$C$20=E3),$B$1:$B$20))

Being array formula they need to be confirmed with Ctrl-Shift-Enter, instead of Enter when exiting edit mode.

7

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy