SOLVED

New Contributor

# Formula to count Alpha Cells

I am trying to count the number of cells that have alpha data in a column.

Column E has either RN, EN or AIN. Column F has alpha data in some rows. I would like a count of how many cells in Column F have data where Column E has RN ... or EN or AIN

I have used =COUNTIFS(E:E,"*RN*",F:F,"<>") but this only works sometimes? and I cannot use the constant \$E:\$E to be able to copy that formula to columns G, H I etc.

Thanks

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Formula to count Alpha Cells

in H1:

``=SUM(COUNTIFS(E1:E10,{"rn","ain","en"}, F1:F10,"<>"))``

# Re: Formula to count Alpha Cells

@Susangreen15 Give a try to the following formula-

`=FILTER(E1:E10000,ISNUMBER(SEARCH(E1:E10000,"RN|AIN|EN"))*(F1:F10000<>""))`

# Re: Formula to count Alpha Cells

Thank you so much - I used L z. (modified just a bit) and it works perfectly - much appreciation for helping me with this

# Re: Formula to count Alpha Cells

Glad this helped & Thanks for providing feedback