In a FORMULA, count an imbedded set of text, in the VISIBLE cells only, of a column.

[RogerP]

I need a means (in an excel formula) to count the number of "visible" cells which have "aka" or "a/k/a" (i.e., also known as) in the address. I currently have the formula of: ="AKA records = "&COUNTIF(E5:E5004,"*aka*")+COUNTIF(E5:E5004,"*a/k/a*"). The result shows ALL records with that text, NOT just the visible ones. I need to count "aka", etc. in JUST the visible cells.

 

[gravanoc]

If you don't mind s little data manipulation then I'd suggest selecting the entire range to be counted, then use the command Goto Special > Visible Cells. Once executed, copy and then paste those visible cells to a new area. Run COUNTIF on newly pasted data.

 

[Fluff]

You could add a helper column with

=SUBTOTAL(3,A2)

change the A2 to a column that will always have data & then you can use

="AKA records = "&COUNTIFS(E5:E5004,"*aka*",Z5:Z5004,1)+COUNTIF(E5:E5004,"*a/k/a*",Z5:Z5004,1)

changing the Z to the column with the subtotal formula