Hello,
I am trying to get a formula to work inside a table, to count how many times text appears. However, I will be filtering the data and therefore would want only visable cells to be counted. Also, the there maybe more text in the box, for example, I will be looking for "Birds" and the text may contain "Lizards, Birds and Fish".
I have tried two different formulas, the first-
=COUNTIF(B2:B10, "*"&B1&"*") Where B1 contains Birds
This returns the correct results, but does not take into account filtering.
The second formula, which I input after changing the data set to a table, as I thought this would help with the filtering process is-
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Table1[Type],ROW(Table1[Type]-MIN(ROW(Table1[Type])),,1))*(Table1[Type]=B1))
This formula works when filtered, however only includes results for exact results, I have tried altering this to end with (Table1[Type]="*"&B1&"*")) but have had no success.
Any help is greatly appreciated.
Thanks you
Bookmarks