Please add MAXIF and MINIF
It would just be so useful to have this. Thanks!
Just wanted to let you know that we’ve released MAXIFS and MINIFS to Excel as part of Office 365. If you’re a subscriber you should already have (or see soon) these functions in your arsenal.
Great news – we’re working on this! Actually, we’re working on MAXIFS and MINIFS – even better :-)
Gerdami Des Betes commented
Completed on 20 April 2016 but only on 365.
On 30 April 2018, still not available on Excel 2016 :-(
Detlef Lewin commented
This should be set to 'Completed' to free the 58 votes.
Patrick O'Beirne commented
Surely simplest is to deprecate all these special case functions and just write one IFS() or FILTER() function to do the selection /filtering then the user can decide what operation to perform on the filtered set: SUM, MAX, AVERAGE, etc.
I see this has been uservoiced already, see the suggestion:
Google Sheets FILTER function to solve all -IFS suggestions
While MAXIFS is definitely the most straightforward, there's the long-term problem of having a pile of functions that are only slight variations of each other. COUNT, COUNTA, COUNTIF, COUNTIFS; now repeat for SUM, MAX, MEDIAN, etc. Then we go from asking "why can't we have MAXIFS" to asking "why are there so many functions that do the same thing". It's similar to this post:
AGGREGATE (which I didn't know about until reading Andreas's comment) is just the opposite: more intimidating but keeps the number of functions less intimidating. I think that AGGREGATE is a step in the right direction, but it still has a few problems.
1. The function is represented by a number (not very readable). Better idea: treat AGGREGATE as a higher-order function and pass it functions rather than numbers. e.g. AGGREGATE(SUM,,)
2. It only works on columns.
3. No non-repetitive way to represent OR.
I still like the idea of a function returning filtered results better. Then you don't need AGGREGATE or new -IFS functions. You just use the regular SUM or MAX or whatever on the returned array. More about that here:
Andreas Thehos commented
Those funktionality is allways there by using AGGREGATE. You can use the Parameters 14-19 and put in several conditions in the array part. But it is not easy to learn like a MAXIFS or MEDIANIFS.
Stephen Eddleston-McGrath commented
Agree with Kenneth Barber re: AGGREGATEIFS - also allows the useful ability to choose what to ignore (errors, subtotals etc)
Instead of all of these -IF and -IFS functions, why not have AGGREGATEIFS? It would be like SUMIFS, but with an extra parameter to choose your aggregate function (sum, product, max, min, average, median, mode, etc.), kind of like the SUBTOTAL function, but without the special effects of subtotal (e.g. ignoring other SUBTOTALs and ignoring filtered out results).
After all, SQL already has plenty of aggregate functions to choose from...
Up vote for MEDIANIF(s)...
I can't share a specific timeframe for the rollout, but the good news is that we ship monthly now, so there's (generally) no more "wait three years" :-)
What about sumproductif ?
Hi Dan - wonderful news! Is there a timeline for rollout? Not holding your feet to the fire, just looking for a rough estimate.
Really pleased about this, thanks for your prompt update.
John Doe commented
Agreed on MedianIf and other additional if functions.
Kara T. commented
Ditto on MedianIF (and IFS)!