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.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments