Countif Spearing

[billcon]

I have a requirement to count if a cell's value in C8:C23 on the third through last sheet in workbook is "vacant". I never know how many sheets there will be.

Currently the formula below works, where SheetList is a named range containing the names of the worksheets. SheetList is somewhat dynamic as the size of the range is set by a macro when the worksheets are created. The sheet names are always as follows
1450-1, 1450-1 (2), 1450-1 (3), etc. I created a list on a hidden sheet of 1450-1 - 1450-1 (100), which there will never be a sheet higher than. SheetList will then refer to HiddenSheet!A1:A(#of 1450-1 sheets).

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C8:C23"),"Vacant"))

I can no longer have hidden sheets. The number of sheets, once created, will never change. I have the following line of code to make a formula elsewhere in the sheet

Worksheets("1450").Range("I13").Formula = "=SUM('1450-1:1450-1 (" & t & ")'!E24)"

t = the number of 1450-1 sheets.

When I tried to use the same concept for the SUMPRODUCT formula it did not work:
=SUMPRODUCT(COUNTIF(INDIRECT('1450-1:1450-1 (20)'!C8:C23),"Vacant"))

I cannot use MoreFunc, named ranges (unless they are refering to C8:C23, but not a hidden named range like SheetList above), and the formulas must remain live.

If you have any suggestions on how to solve this problem, please let me know.

Thanks,

Bill

 

[Domenic]

First, rename sheet 1450-1 to 1450-1 (1) so that the format is consistent with your other sheet names. Then try...

=SUMPRODUCT(COUNTIF(INDIRECT("'1450-1 ("&ROW(INDIRECT("1:20"))&")'!C8:C23"),"Vacant"))

Or, if you don't want to modify the formula each time a new sheet is added and the number of sheets will never exceed 100, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(ISNUMBER(COUNTIF(INDIRECT("'1450-1 ("&ROW(INDIRECT("1:100"))&")'!C8:C23"),"Vacant")),COUNTIF(INDIRECT("'1450-1 ("&ROW(INDIRECT("1:100"))&")'!C8:C23"),"Vacant")))

or

=SUM(IF(ISNUMBER(N(INDIRECT("'1450-1 ("&ROW(INDIRECT("1:100"))&")'!C8"))),COUNTIF(INDIRECT("'1450-1 ("&ROW(INDIRECT("1:100"))&")'!C8:C23"),"Vacant")))

 

[billcon]

Domenic,

Thank you. That will work for me.

I am curious - how might one approach this if the sheet names do not have a consistent format?

Again, thanks for the help.

 

[Domenic]

For that scenario...

=SUM(COUNTIF(INDIRECT("'1450-1"&IF(ROW(INDIRECT("1:20"))>1," ("&ROW(INDIRECT("1:20"))&")","")&"'!C8:C23"),"Vacant"))

or

=SUM(IF(ISNUMBER(COUNTIF(INDIRECT("'1450-1"&IF(ROW(INDIRECT("1:100"))>1," ("&ROW(INDIRECT("1:100"))&")","")&"'!C8:C23"),"Vacant")),COUNTIF(INDIRECT("'1450-1"&IF(ROW(INDIRECT("1:100"))>1," ("&ROW(INDIRECT("1:100"))&")","")&"'!C8:C23"),"Vacant")))

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

 

[billcon]

Domenic,

Thank you for the assistance.

What if I have to find a string within a given cell. So instead of "Vacant" I would be looking for any cell containing "(C/T)." I have tried working a SEARCH formula into the solution already provided but I have not been able to make it work yet. Any thoughts?

Thanks,

Bill

 

[Domenic]

Simply replace...

"Vacant"

with

"*(C/T)*"

or

"*"&A2&"*"

...where A2 contains (C/T).