COUNTIF question

[KrisRagu]

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

[#2]

@KrisRagu 

=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.

[#3]

that should work if the value in A2 is text that you want to find in all text cells but if it is a value (i.e. number) then that doesn't work. For example if you want to find all cells that have a 7 in the number like 17, 74, 107, etc... that will not work and you need to force the cells to be read as text first. Please clarify what you need and what exactly isn't working and maybe include a sample sheet showing how it isn't working and what it should be.

[#4]

@mtarler 

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

[#5]

did the original find 1? That 3rd occurrence looks like text so how about a combined formula?
= SUMPRODUCT(
COUNTIF(INDIRECT( &mysheets&"!A2:Z1000"),A3) +
COUNTIF(INDIRECT("'"&plans&"'!"&"1:1048576"),"*"&A2&"*")
)
note I just copy and pasted your 2 formulas as the 'concept' and leave it to you to correct the sheet references and such.