Countifs/Sumproduct for filtered data, searching if cell contains text

[Bellio3105]

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

[Kaper]

Try for instance:

Please Login or Register  to view this content.

PS. As we have now FORMULA tags available - please consider using them in the future (not yet required by forum rules).

[Bellio3105]

Apologies for the late reply.

Thank you for the reply, however it seems to return zeroes.

Any reason why this may occur?

Thanks,

Dan

[Kaper]

No idea. 2 days ago I prepared tiny sample file to test it. It worked. But then I deleted file.

[newdoverman]

Can you post your workbook so that potential answers are appropriate to the problem posted?

[Bellio3105]

Hi,

Attached is the workbook.

If you look at the summary page, on the second row is where I am currently using the countif formula, however this does not alter as I filter the table in the 1st tab.

Any help is much appreciated.

Thanks,

Dan