Forum Discussion
COUNTIF question
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(IN...
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
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.
= 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.