Trying to create a MAX IF Formula

[ChiScott27]

Good Afternoon,

I am trying to create a "MAX IF" array formula without any luck. I am trying to find the maximum value in a given range based on a key word criteria and two date restrictions.

Below is the formula I have so far.

=max(if(('Data Set'!$E:$E=$H16)*('Data Set'!$G:$G)*('Data Set'!$B:$B,">="&I$13+0)*('Data Set'!$B:$B,"<="&I$14+0),('Data Set'!$G:$G)))

range $E:$E is searching for a key term
range $G:$G is the range I want to find the the maximum value in
range $B:$B is the range that contains dates

Any help with this would be appreciated.

 

[AD_Taylor]

Try,

=MAX( IF( AND( 'Data Set'!$E:$E=$H16, 'Data Set'!$B:$B,">=" & I$13 + 0, 'Data Set'!$B:$B,"<=" & I$14 + 0), 'Data Set'!$G:$G, ""))

As I'm sure you're aware you'll need to press Ctrl + Shift + Enter instead of just Enter to confirm the array formula.

 

[JoeMo]

Untested.
=MAX(IF(('Data Set'!$E:$E=$H16)*('Data Set'!$B:$B>=I$13+0)*('Data Set'!$B:$B<=I$14+0),('Data Set'!$G:$G)))
confirm with shift+ctrl+enter

 

[Jonmo1]

This isn't a countif or sumif formula.
You don't need the "<="&cell syntax..
And you had an extra reference to the G Column
And you don't need the () around the last G column reference.

try
=max(if(('Data Set'!$E:$E=$H16)*('Data Set'!$B:$B>=I$13+0)*('Data Set'!$B:$B<=I$14+0),'Data Set'!$G:$G))

 

[ChiScott27]

Thank you AD_Taylor, JoeMo, and Jonmo1 for your help. The formula Jonmo1 and Joemoe supplied me both gave me the same answers. I may have did something wrong with your formula AD_Taylor, but I appreciate all your help, thanks again for taking the time to help me out.