Filter responsive count of entries with a given fragment of text

[Seed1]

G'day Excel forum

I'm having some trouble with a relatively simple equation, I'm attempting to generate a count of all cells that contain a certain text segment regardless of whether it is standalone or set within other text (I've attached a simplified excel sheet example to clarify my nonsensical ramblings), this would be simple on its own (Example 1) however the cells from which this count needs to be calculated are subject to a filter and the results of the traditional method are unchanged when the filter is applied. This can be remedied with a sum product approach (Example 2) however this has the drawback of being unable to select text if it is set within other text (the ("*"&cell reference&"*") approach).

What is needed, therefore, is some way of calculating the subtotal of those cells containing the text (Example 2) that can be calculated on filtered entries for data that may contain more text than just that which is being counted.

Anyone out there have any inkling as to how one would pull this off?

[benishiryo]

hi there. welcome to the forum. is this formula in H4 what you need?

Please Login or Register  to view this content.

[Seed1]

Not quite, that formula almost does the job however it cannot sum the cells that have text other than the text i'm trying to count, i.e. I need to count all cells with the values; "Apple", "Apple, Banana" and "Banana, Apple", this equation will only count "Apple"

[Seed1]

I've updated the spreadsheet example with some notes, hopefully this will make it clearer...