Indirect with countifs

Hi.

I've got a spreadsheet with multiple sheets. The front sheet is a summary sheet pulling certain information from the others using the indirect function i.e.

Please Login or Register  to view this content.

where A7 has the sheet name typed in


I now need to include countifs using multiple criteria. I have managed to come up with the following, but I want to incorporate the indirect function so I don't have to keep changing the formula to include each sheets name

Please Login or Register  to view this content.

Can you please help me?

Thanks

Hi,

Not sure, but try below formula:

=SUMPRODUCT((INDIRECT(A7&"!$AM$9:AM$2000")="no")*((INDIRECT(A7&"!$AN$9:AN$2000")="for Sale")+(ISNUMBER(SEARCH("*on*",INDIRECT(A7&"!$AN$9:AN$2000"))))))

Regards,

Hi

This works for all sheets where there is only one word as the sheet name, so for example "Colchester" in A7 and on the sheet name.

Where there are two words in A7 and the sheet name I get #REF!, so where it is "Milton Keynes" in A7 and on the sheet name.

Any ideas?

Thanks.

Replace Indirect as below and change AM to AN.

INDIRECT("'"&A7&"'!$AM$9:AM$2000")

Thanks all, they work perfectly