Count Text Across Worksheets

[dwgnome]

Hi,

Trying to add all of occurrences of "Yes" in a particular range for all worksheets.

I was hoping to use the clever trick I use for summing across worksheets:

=SUM(First:Last!$E$36:$I$36)

I tried using COUNTIF or AGGREGATE (using 2 for COUNT) but I get #VALUE ! error.

=COUNT(First:Last!$C$48) or
=COUNTIF(First:Last!C48,"?*")

I am aware of the approach using INDIRECT and a SHEETLIST and reference the worksheets. However, the workbook is quite dynamic as the Rockwell Software program creates a new daily worksheet each day and populates that worksheet with various data.

Before I give up and just change the formula to display a 1 instead of Yes, I was hoping to keep the text and perhaps learn why the First:Last trick may not work with text or learn a new approach.

Excel 2010, Windows 10

 

[Michael M]

This may help...with step by step process

https://exceljet.net/formula/count-occurrences-in-entire-workbook

 

[Aladin Akyurek]

Define SheetList using Insert | Name | Define or Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Now you can count the Yes occurrences across sheets automatically listed in SheetList…

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!E36:I36"),"yes"))


Count text entries which is in C48 across all sheets which are listed in SheetList…

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C48"),"?*"))

 

[dwgnome]

Hi Michael,

Thank you for suggestion. I have seen that construct before. I tried using that approach but it gives me #REF! error. I believe that in order for it to work, all worksheets listed in the named range must be present. Since my sheetnames are actually the day of month starting with 1 and ending with 31, I created the named range using those names (even though they are actually numbers) in a range called Sheet. The problem is when the any of the worksheets in the range doesn't exist in the workbook, it fails. Like I mentioned, the workbook is dynamic with a new sheet added each new day. Thus the Sheet named range needs to be dynamic.

Aladin, I tried your technique and it worked right away. I suspect that the SheetList defined name is dynamic in nature.

However, when I tried to save the file, it gives a warning “The following features cannot be saved in macro-free workbooks”. It says Excel 4.0 function stored in defined names. I am forced to save the file as a macro-enabled file type. However, the template that the Rockwell Software uses can only be regular Excel file. Is there a way around that?

 

[Aladin Akyurek]

[…]

Aladin, I tried your technique and it worked right away. I suspect that the SheetList defined name is dynamic in nature.

However, when I tried to save the file, it gives a warning “The following features cannot be saved in macro-free workbooks”. It says Excel 4.0 function stored in defined names. I am forced to save the file as a macro-enabled file type. However, the template that the Rockwell Software uses can only be regular Excel file. Is there a way around that?

I'm afraid there is no choice here. Excel 4.0 macro or straight vba both require a macro-enabled workbook.

 

[dwgnome]

That's okay. However I will keep your solution in mind just in case.
Thank you.