Pulling out "a"

[Gary Thomson]

How do I count the number of times "a" appears within the
following:

UNAVAILABILITY
A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths a a
3 English a ab ab
4 Music abc b abcde
5 P.E.
6 Geography
7 History
8 Drama
9 Science a
.. .
.. .
.. .

 

[Max]

Try : =COUNTIF(B2:E9,"*a*")

 

[Gary Thomson]

Cheers Max, but is there a way that I can copy and paste
this formula down for "b", "c", "d", "e", etc, without
replacing the "a" manually everytime?

 

[Max]

Assuming you have in A11 downwards
A11: a
A12: b
A13: c
etc

Put in B11: =COUNTIF(B2:E9,"*"&A11&"*")
copy down

 

[Peo Sjoblom]

=COUNTIF($B$2:$E$9,"*"&CHAR(ROW(97:97))&"*")

will do it, copy down using the fillhandle until you reach 122 (z)

 

[Max]

oops, correction (missed out fixing the range B2:E9)

Put in B11: =COUNTIF($B$2:$E$9,"*"&A11&"*")

 

[Gary Thomson]

Cheers Max - Question though - why do we need the "*" -
what does the * actually do?

 

[Max]

The asterisk "*" is a wildcard, to search for anything joined with a target.
In your case, the targets are the letters a, b, c, etc within the range.
cheers.