# max and min if while excluding date range, array formula

#### twinwings

##### Board Regular
Hello,

I would like to calculate the max and min of a row of numbers, while excluding those numbers that correspond to a date range

Row 3, or range C3:JO3 is the row with ALL the dates
tab excludelist, range \$AF\$84:\$AF\$95 is the range of dates that I want to exclude from the max/min calculation
C186:JO186 is the row that the calculation is based upon.

I thought I had this, but the more I google, the more confused im getting.

I don't understand what I am missing in the below?

=MIN(IF(C3:JO3<>'excludelist'!\$AF\$84:\$AF\$95,C186:JO186)

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Weazel

##### Well-known Member
maybe something like....

=MIN(IF(ISNA(C3:JO3<>excludelist!\$AF\$84:\$AF\$95),C186:JO186)) control shift enter

#### Canapone

##### Active Member
Hi,

array entered (control+shift+enter)

=MIN(IF(COUNTIF('Excludelist'!\$AF\$84:\$AF\$95,
C3:JO3)=0,C186:JO186)

If you'd be an Excel 2010 (or newer versions) user:

=AGGREGATE(15,6,
C186:JO186/(COUNTIF('Excludelist'!\$AF\$84:\$AF\$95,C3:JO3)=0),1)

Hope it helps

Last edited:

Replies
0
Views
166
Replies
1
Views
225
Replies
3
Views
2K
Replies
6
Views
217
Replies
6
Views
659

1,191,695
Messages
5,988,147
Members
440,130
Latest member
bianca88

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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