CODEWITHSUNDEEP
excelsyntaxexcel-formulaworksheetexcel-indirect
Or s
Or s

Reputation: 11

Indirect using variable range for Worksheet names

I have the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:D2&"'!A1"),E2))

Took it from here https://www.extendoffice.com/documents/excel/2541-excel-countif-across-multiple-worksheets.html

Now, i'm making a COUNTA to the D column:

=COUNTA(D:D)

Which will basically result in me having the number of sheets.

Now, i would like to make the first formula use the result of the second one:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:***COUNTA RESULT***&"'!A1"),E2))

What is the correct syntax to perform that? Did not manage to succeed.

Upvotes: 1

Views: 86

Answers (1)

user4039065
user4039065

Reputation:

Since worksheets typically have alphabetic names and you were attempting a solution with COUNTA not COUNT, use, 

D1:index(D:D, match("zzz", D:D))

... to describe your range of worksheet names.

However, if the worksheet names are numeric then,

D1:index(D:D, match(1e99, D:D))

Completed as,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:index(D:D, match("zzz", D:D))&"'!A1"),E2))
=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:index(D:D, match(1e99, D:D))&"'!A1"),E2))

You could also use OFFSET(D1, 0, 0, COUNTA(D:D), 1). Since you are already using a volatile function with INDIRECT, adding another isn't that big of a deal.

Upvotes: 1

Related Questions