HELP! Excel 2010 | Unexpected results using MIN IF Array Formula with COUNTIFS

Hi All,

I've been trying to figure out this issue for a couple of days now to no avail. Therefore, I'm hoping someone can kindly shed some light on it. I've attached a sample spreadsheet.

I have 2 worksheets - 'Summary' is a cover sheet listing a Package field and then a Required By and Required Until field. The 'Component List' worksheet lists the names of components associated with a Package containing a Required By and Required Until column. What I'm trying to do is workout the minimum date for all components as part of a package in column C on 'Summary' and the maximum date for all components as part of a package in column D on 'Summary'.

As I'm using Excel 2010, I'm using MIN and IF in an Array Formula. The formula I've come up with for the Required By date in Summary (column C) is as follows:

{=IF(COUNTIFS('Component List'!$B$5:$B$224,Summary!$B$5:$B$19,'Component List'!D$5:D$224,"<>"&"")>0,MAX(IF('Component List'!$B$5:$B$224=Summary!$B$5,'Component List'!D$5:D$224)),"TBC")}

The logic behind is IF there are any blanks within column C on 'Component List' for the components in said package (Coulmn B) then set the cell value as "TBC". If there aren't any blank within column C on 'Component List' for the components in said package (Coulmn B) then set the cell value as the minimum date.

As you can see from the spreadsheet, I've highlighted the cells that are bring back the unexpected value. Is it a case that I'm using the COUNTIFS within an Array Formula (the curly brackets)? Any help would be very much appreciated.

Cheers,

Al

Do not use a link to a file-sharing site, please. Instead, attach a sample workbook here. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks. Personally, I won't open macro-enabled workbooks - sorry. Hopefully someone else will help.