Forum Discussion

azeoli1's avatar
azeoli1
Copper Contributor
Nov 09, 2021

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 

Formulas and Functions

4 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 09, 2021

    azeoli1 

    =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.

    • azeoli1959's avatar
      azeoli1959
      Copper Contributor
      Nov 15, 2021
      I figured a simpler formula: COUNTIF(B48:B141, “A”). Thanks anyway Hans.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 15, 2021

        azeoli1959 

         

        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...

Resources