Conditional summing across multiple sheets

Hi,

I am trying to sum the same cell across multiple sheets, but want to incorporate a summing criteria. Ordinarily I would use '1:3'!C23 (for example) to sum the same cell (C23) across sheets 1 to 3.

However, say C23 on sheet 2 doesn't fit my criteria (such as cell C1 on all sheets being the name of a client), yet C23 on sheets 1 and 3 do - is it possible to use this type of formula and incorporate a criteria such that the result would be sum('1'!C23,'3'!C23)?

So, this is the formula I came up with after reading multiple threads:

=SUMPRODUCT(SUMIF(INDIRECT("`"&PropertyList&"`!$A$2"),"Crestline",INDIRECT("`"&PropertyList&"`!C12")))

And then updated to

=SUMPRODUCT(SUMIF(INDIRECT("`"&PropertyList1&"`!$A$2"),"Crestline",INDIRECT("`"&PropertyList1&CELL("address",C12))))

After all that work, I still get #ref error.... I went to "evaluate formula" and it seems to work fine until -INDIRECT("`"&PropertyList1&"`!$A$2")- this part shows #ref at the end of the step.

Do you guys see anything odd or wrong in the formula?

Please let me know.
Thank you in advance.

Capture.JPGCapture1.JPG

Attach a sample workbook (not image).

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.