Nov 04 2021 07:20 AM
I need to count the occurrence of a value in a cell in Sheet1.... the search should include all sheets in the workbook that "contain" the value .
Tried this formula and it doesn't work:
=COUNTIF(INDIRECT("'"&plans&"'!"&"1:1048576"),"*"&A2&"*")
thanks...Kris
Nov 04 2021 08:08 AM
=SUM(COUNTIF(INDIRECT("plans"&ROW(1:2)&"!A1:XFD1048576"),"*"&A2&"*"))
If entered as arrayformula with ctrl+shift+enter in "sheet1", formula counts text values entered in A2 (in "sheet1") in spreadsheets named "plans1" and "plans2" in range A1:XFD1048576 (partial match as intended). Formula can easily be adapted to more spreadsheets named plans1, plans2, plans3, plans4 and so on.
Nov 04 2021 08:15 AM
Nov 04 2021 12:12 PM - edited Nov 04 2021 12:18 PM
Hi..
Pic attached!
I want to find using formula the same 3 occurrences of the number (ClientID) in the whole workbook. Wild card didn't work eg: "*"&A3&"*")
= SUMPRODUCT(COUNTIF(INDIRECT( &mysheets&"!A2:Z1000"),A3))
Where A3 would be the cell with text value: 90310. Need all cells that 'contain' 90310...even if part of a long string ...Currently formula only returns 2 cells.
(btw I know how to select sheets and group them as "mysheets"if needed to search only select tabs.)
Thanks, Kris
Nov 04 2021 02:54 PM