Find MIN & MAX values based on an IF condition for an array.

Hi guys,

I have 3 projects with various number of tasks involved in them with start and end date for each task in sheet 1. I need help to calculate Start and end date automatically for each project in sheet 2 based on data from sheet 1.

I have attached an example file. I couldn't figure out how to use IF conditions using MIN & MAX functions.


Thanks in advance.

Use this array* formula in C3:

=MIN(IF(Sheet1!$B$3:$B$34=$B3,Sheet1!D$3:D$34))

and this array* formula in D3:

=MAX(IF(Sheet1!$B$3:$B$34=$B3,Sheet1!E$3:E$34))

then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >

Hope this helps.

Pete

Thanks Guys.

Formula kinda worked.

Sorry, I forgot to mention that in my original sheet I have arrays with 1000+ values incl blanks. Right now, as there are blanks, MIN value is becoming "Zero". So formula shall exclude blanks in the array.

MAX function worked perfectly.

You can use this array* formula in C3 instead:

=MIN(IF((Sheet1!$B$3:$B$34=B3)*(Sheet1!$D$3:$D$34<>""),Sheet1!$D$3:$D$34))

to ignore blank entries. Adjust the cell references to $34 (3 times) to suit your actual data.

*Remember to use CSE to commit the formula, as previously advised, and then copy down.

Hope this helps.

Pete

Hi Pete,

Your formula worked.

I haven't tried other two. Will give a try.

Thanks everyone.

You're welcome - glad to have helped.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete