How to find how many times a certain string appears in the whole Excel workbook in certain columns?-CodePudding

The workbook has 52 sheets. Data I want to search is in columns F, I, L ... AM in each sheet. I must not count data in any other column. Data is string of three characters for example NOR or HEI. How do I find how many times (for example) NOR appears in that non-adjacent range?

I used function countif. I tried Naming the non-adjacent range but could not get it to work. I tried sum(countif(distinct(arguments))) but could not get it to work.

CodePudding user response：

If you first create a vertical list of all your sheet names, called SheetList, for example, you can then use:

=LET(
    ξ, COLUMN($F:$AM),
    ζ, SUBSTITUTE(ADDRESS(1, FILTER(ξ, MOD(ξ - MIN(ξ), 3) = 0), 4), 1, ""),
    SUMPRODUCT(
        COUNTIF(INDIRECT("'" & SheetList & "'!" & ζ & ":" & ζ), "ABC")
    )
)

Change the "ABC" at the end to whatever string you want to search for.

Edit: for older versions of Excel:

=SUM(COUNTIF(INDIRECT("'" & TRANSPOSE(SheetList) & "'!" &
SUBSTITUTE(REPT(SUBSTITUTE(ADDRESS(1,
MODE.MULT(IF(MOD(COLUMN($F:$AM) -
MIN(COLUMN($F:$AM)),3) = {0;0},
COLUMN($F:$AM))),4),1,"") & ":",2),":","",2)),"ABC"))

which will most likely require committing with CTRL SHIFT ENTER.