Excel formula help!

Hi Tracie, I see you tagged '365' so you have access to VSTACK. This will make things easier and INDIRECT is not needed.

First, use VSTACK to combine all the sheets by supplying a 3D reference.  Next, determine if the 'input' (the cell containg the text) is equal to the 'stack' (the combined ranges).  The result will be a matrix of TRUE and FALSE.  N converts those to 1s and 0s then SUM adds them up.

=LET(
    stack, VSTACK(Sheet1:Sheet3!$A$1:$J$30),
    SUM(N(input = stack))
)