CODEWITHSUNDEEP
excelexcel-formula
micxid
micxid

Reputation: 25

MAX IF formula with range criteria

I'm facing a problem with trying to have max value with multiple criteria. The issue is that one criteria should be named range (TYPE) which contents will change. Data is numbers and text. Of course the whole formula is array formula.

=MAX(IF($C$2=$B$27:$B$509,IF($F7=$F$27:$F$509,IF($C$27:$C$509=TYPE,H$27:H$509)))

I tried to somehow implement OR function but it doesn't work properly. The thing with TYPE range is that it contains up to 3 text variables, user should be able to pick 3,2, or 1 of them and the order of these variables should be flexible. Any help would be greatly appreciated. Thanks!

EDIT

My desired result in this case is 14%. I wish formula to search for max value only among types b, c (TYPE named range) but the result is 0%. The formula now looks like this:

=MAX(IF($G$4=$B$4:$B$20,IF($H4=$D$4:$D$20,IF($C$4:$C$20=TYPE,E$4:E$20))))

sample data extract

Upvotes: 2

Views: 782

Answers (1)

Forward Ed
Forward Ed

Reputation: 9894

Based on your sample data, this formula should work for you:

=AGGREGATE(14,6,($E$4:$E$15*($B$4:$B$15=$G$4)*($C$4:$C$15=$H$4)*(($D$4:$D$15=$G$8)+($D$4:$D$15=$G$9)+($D$4:$D$15=$G$10))),1)

Proof of Concept

Proof of Concept

Functions used:

Caveat:

  • If a type in you data source is blank, and a type picked is also blank, then the data will be included as acceptable provided year and channel match as well.
  • Excel 2010 or later.

Upvotes: 0

Related Questions