Please help, I am struggling with a formula Concatenating across multiple worksheets. My workbook contains 20 worksheets named Legal1-Legal20 and a summary sheet. Each worksheet has identical column headings. My goal is to Concatenate the contents of Legal1:Legal20 Cell $D$2(same in all worksheets) IF the text in Legal1:Legal20 Cell $H$2 (same in all worksheets) reads "Open of Record". The results should be seperated " and "(ex. NW/4 and E/2 SW/4 and NE/4). Please note that cells my be blank on one or more worksheets. Secondly it is possible that only 1 cell meet the criteria and therefore should not be prceeded with " and ".

I have successfully come up with a formula using a similar situation above utilizing SUMPRODUCT noted below. The formula below sums the contents of cell meeting the criteria Of $h$2 from worksheets 1-20.

=SUMPRODUCT(SUMIF(INDIRECT("'Legal"&ROW(INDIRECT("1:20"))&"'!H2"),"Open Of Record",INDIRECT("'Legal"&ROW(INDIRECT("1:20"))&"'!I2")))

Thanks,