Count number of X's in a specific cell

[WillWilco]

How can I create a formula that will count the number of X's in a specific cell on multiple worksheets?
I have 10 worksheets with data that shows if a specific item has been researched or not for multiple students.
The item is located in the same cell on each sheet and I just want to create count of the number of occurrences of the letter X in that specific cell for each student.
Thank you in advance,
Will

 

[KRice]

try...

Book1
	A	B
1	tjX,myX,XrayX	4
2	tjX,myx,xrayX	4
Sheet3

Cell Formulas
Range	Formula
B1	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,"X",""))
B2	B2	=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X",""))

Are the "X's" always capitalized? If just one case, then the 1st formula, with the appropriate character. If mixed cases, then the second formula.

 

[jolivanes]

The X's are in Cell B2.

=COUNTA(Sheet1:Sheet10!B2)

 

[StephenCrump]

@WillWilco , welcome to the Forum!

I interpreted the question similarly to @jolivanes: Items 1 to 10 across the 10 worksheets, Student 1 in A1, Student 2 in A2 etc

But if there are other possibilities than "X" or blank, so that COUNTA won't work reliably ...

1. If you have the new VSTACK function, you could do something like:

=SUM(--(VSTACK(Sheet1:Sheet10!A1)="X"))

2. Otherwise, you'll need to create a sheet array and use INDIRECT. Or if your sheets have consecutive numbering, you could do something like:

=SUM(COUNTIF(INDIRECT("Sheet" &SEQUENCE(10) &"!A1"),"X"))

 

[Peter_SSs]

Another approach

Assuming a single character, or nothing, is in A1 of each sheet & you do not yet have VSTACK.

=LET(s,UPPER(CONCAT(Sheet1:Sheet10!A1)),LEN(s)-LEN(SUBSTITUTE(s,"X","")))

If A1 could include multiple characters (eg "XYZ" which should not be counted even though it contains an X?) then

=LET(s,UPPER("|"&TEXTJOIN("||",,Sheet1:Sheet10!A1)&"|"),(LEN(s)-LEN(SUBSTITUTE(s,"|X|","")))/3)

 

[jolivanes]

For some of us who might still be widening their horizon of knowledge it would be nice to hear what worked the best to fix your problem.