 # CREATE A FORMULA TO ADD HOW MANY TIMES THE LETTER A APPEARS IN A COLUMN OF CELLS

I have created a excel spread sheet that has columns of data and I simply want to create a formula in a cell that will add all the times the letter A appears in a range of cells in a given column. Can someone help me with that?

Thanks,

Al

4 Replies

# Re: CREATE A FORMULA TO ADD HOW MANY TIMES THE LETTER A APPEARS IN A COLUMN OF CELLS

=SUM(LEN(range))-SUM(LEN(SUBSTITUTE(range,"A","")))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

# Re: CREATE A FORMULA TO ADD HOW MANY TIMES THE LETTER A APPEARS IN A COLUMN OF CELLS

I figured a simpler formula: COUNTIF(B48:B141, “A”). Thanks anyway Hans.

# Re: CREATE A FORMULA TO ADD HOW MANY TIMES THE LETTER A APPEARS IN A COLUMN OF CELLS

Your formula will return the number of cells whose value equals "A".

You asked for a formula that returns the number of times the letter "A" occurs. I interpreted this to mean that if a cell contains "ABACADABRA", it should contribute 5 to the result since there are 5 A's...

# Re: CREATE A FORMULA TO ADD HOW MANY TIMES THE LETTER A APPEARS IN A COLUMN OF CELLS

I see. I should have been more clear. I’m my worksheet the column I’m trying to extract this information from only has one letter (A-M) in each cell. Thanks!