SOLVED

# Simple formula for search the min date with criteria

Occasional Contributor

# Simple formula for search the min date with criteria

Hi All, the screenshot are the sample of raw data and result.  I would like to return the earliest date from the category of "pull,push,pull-M,push-M" with formula at K column.

3 Replies

# Re: Simple formula for search the min date with criteria

If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in K111:

=MINIFS(\$G\$111:\$G\$500, \$F\$111:\$F\$500, J111, \$H\$111:\$H500, "pu*")

Adjust the ranges if your data extend below row 500, then fill down.

If you have an older version of Excel, enter the following formula in K111 and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

=MIN(IF((\$F\$111:\$F\$500=J111)*(LEFT(\$H\$111:\$H500, 2)="pu"), \$G\$111:\$G\$500))

and fill down.

# Re: Simple formula for search the min date with criteria

Thanks for the help on it. My excel is newer version, with Minifs will return 0-Jan-00 if not found or match, I would like to have show blank or empty if not found.
best response confirmed by MeiChan1314 (Occasional Contributor)
Solution

# Re: Simple formula for search the min date with criteria

Use

=IF(MINIFS(\$G\$111:\$G\$500, \$F\$111:\$F\$500, J111, \$H\$111:\$H500, "pu*")=0,"",MINIFS(\$G\$111:\$G\$500, \$F\$111:\$F\$500, J111, \$H\$111:\$H500, "pu*"))