Excel 2019: Find Largest Value That Meets One or More Criteria
October 09, 2019 - by Bill Jelen
One of the new Office 365 functions added in February 2016 is the MAXIFS function. This function, which is similar to SUMIFS, finds the largest value that meets one or more criteria: You can either hard-code the criterion as in row 7 below or point to cells as in row 9. A similar MINIFS function finds the smallest value that meets one or more criteria.
While most people have probably heard of MAX and MIN, but how do you find the second largest value? Use LARGE (rows 2 and 3) or SMALL (rows 4 and 5).
What if you need to sum the top seven values that meet criteria? The orange box below shows how to solve with the new Dynamic Arrays. The green box is the Ctrl+Shift+Enter formula required previously.
Title Photo: Charles at Unsplash.com