CODEWITHSUNDEEP
excel
marco
marco

Reputation: 826

Filtering in formulas in Excel

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.

Upvotes: 0

Views: 100

Answers (1)

Andy G
Andy G

Reputation: 19367

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.

Upvotes: 3

Related Questions