# Trying to create a MAX IF Formula

#### ChiScott27

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

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

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))

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.

Replies
2
Views
108
Replies
8
Views
230
Replies
10
Views
272
Replies
2
Views
157
Replies
24
Views
271

1,203,535
Messages
6,055,964
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back