I need function, that counts sheets, in which were used my word. Example: have 100 sheets, and i need to findout, that word "CAR" is used in 76 of them. Currently, im using formula below to findout all counts of that word.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SHEETSNAME!$A$2:$A$102&"'!$A$1:$Z$10000");"*"&A1&"*"))

Is there something like that?

CodePudding user response：

Simply amend your current formula to:

=SUMPRODUCT(N(COUNTIF(INDIRECT("'"&SHEETSNAME!$A$2:$A$102&"'!A1:Z10000");"*"&A1&"*")>0))