Sep 28 2022 02:49 AM
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
Sep 28 2022 05:30 AM
SolutionSep 28 2022 05:46 AM
@Susangreen15 Give a try to the following formula-
=FILTER(E1:E10000,ISNUMBER(SEARCH(E1:E10000,"RN|AIN|EN"))*(F1:F10000<>""))
Sep 28 2022 12:33 PM
Sep 28 2022 12:54 PM
Sep 28 2022 05:30 AM
Solution
in H1:
=SUM(COUNTIFS(E1:E10,{"rn","ain","en"}, F1:F10,"<>"))