I know I just asked a somewhat similar question about SUMPRODUCT IFS, but I still can't figure this one out...
I need to find the MAXIFS of column F where column C = either "Apples", or "Bananas".
=MAXIFS(F$8:F$75,$C$8:$C$75, "Apples" or "Bananas")
Oh, and then I need to return the address of that cell.
There is no MAXIF/S() function (yet)
Try something like this ARRAY formula...
=MAX(IF($C$8:$C$75={"Apples","Bananas"},F$8:F$75))
(untested)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There is MAXIFS and MINIFS in my Office 365 version of Excel, Ford! However, it's not available in earlier versions.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Im using Office 2016 pro, and that is not (yet) included inthere
Try
=MAX(MAXIFS(F$8:F$75,$C$8:$C$75,{"Apples","Bananas"}))
I'm guessing that those of us not paying for the overpriced office 365 subscription will have to wait until the 2019 is released.
What's overpriced about it? I don't consider £79 for five installations unreasonable (that's lest than £20 per machine).Originally Posted by jason.b75
Anyway, just for completeness, your formula works in Office 365.
It probably works better for some, than it does for others, Ali.
What you consider to be reasonable assumes that you are going to use all 5 installations.
Also, bear in mind that the price you pay only gets you a year subscription, not a product to keep as long as needed.
The home version of 2007 that I installed on 2 machines served its purpose for me for around 8 years, I can't remember the exact cost, but say it was £120, that works out at £7.50 per machine per year
MS promote 365 as a way to get the latest updates, but given that MAXIFS() etc. were in the initial release of the 2016 suite to 365 subscribers (or at least an update very soon after), it would be fair to assume that these functions would have been ready to release with the regular versions of 2016, but that MS held them back.
Not so much an update, more a premium feature that the rest of us don't have access to!
No doubt MS will soon be applying the same money making model to Windows
Yes, true, but in our household it works: the main desktop PC, my two laptops, my partner's laptop and my daughter's laptop, plus using it on three iPads between us.
Is there any room for the people in your house, Ali??
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Plenty.
I realize this is quite an old post, but is there a way to accomplish this while referencing cells instead of text? For example (this doesn't work BTW):
=MAX(MAXIFS(Data!G:G,Data!$A:$A,English!$B11,Data!$B:$B,{"$A$2","$A$3","$A$4"}))
Ultimately I'd like to something similar to the following which captures all data meeting a date criteria and any criteria in A2:A100 (which happen to be skill numbers).
=SUMPRODUCT(SUMIFS(Data!D:D,Data!$A:$A,English!$B11,Data!$B:$B,English!$A$2:$A$100))
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
As per forum rule #4, please do not post a question in an existing thread, please start your own.
Very useful. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks