0

I am aware of the SUMIF (and friends) formula. I am wondering if it's possible to extend the logic to other functions (e.g. "maximum age where gender=F") without macros or VBA. I tried things like

MAX(IF(A1:A5="F",B1:B5,""))

but it doesn't seem to work.

Improve this question
1
  • If you have multiple criteria you should read this DDOE post. Commented Oct 22, 2013 at 21:16

1 Answer 1

Reset to default
3

It can be done with an array-formula, use Ctrl-Shift-Enter to enter this formula:

=MAX(IF(A1:A5="F",B1:B5,0))

(it will display in the formula-bar with curly-brackets).

The zero is used for those values/rows that aren't "F", which won't affect the maximum age.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

.but this question should be in superuser.com because you don't require any coding. This is a programming forum.
@AndyG Not saying your comment is wrong, but a serious question, I am very used to people redirecting questions to SuperUser, yet on Excel Info page it seems to imply that questions for Formulas are exceptable if tagged as Excel-Formula
My bad, I was missing the Ctrl-Shift-Enter bit. Thanks! (edit: yes, cmd+enter works too!)
@user2140261 It says that Excel-formula is a "more specific tag". A question should be about programming but could include this tag to indicate that it specifically relates to a formula, perhaps using VBA to insert a formula into a cell. Someone more senior might clarify my understanding. It is true, though, that many formula (non-programming) questions are still answered here - mainly when they are straight-forward, and it causes less hassle to just answer them :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.