Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like:

Applepie
Applesauce
Crabapple
Banana
Mango

What I need is the count of all visible cells containing the word "Apple". Note that this should exclude the hidden (filtered out) cells.
Now I have found out a formula:

`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`

However the problem with this one is this formula searches for the entire text. NOT just a part of it. How do I modify this formula to return count of cells containing "Apple" i.e. for this example it should return 3 as the result.

Gh0sT

Posted 2013-12-02T13:10:24.530

Reputation: 409

Do you expect 3 as the result?? – Gary's Student – 2013-12-02T16:29:01.013

In this case...yes. – Gh0sT – 2013-12-02T16:44:00.170

Answers

COUNTIF with wildcards will work alone to count all rows with "Apple" somewhere in the text......but it isn't restricted to visible rows and you can't use COUNTIF in conjunction with your SUBTOTAL formula.

Adjust your formula like this using SEARCH function

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)

Note: that version may be slow, referencing the whole column - better to restrict the ranges if you can

Alternative approach

If you want to avoid that long formula then you could use a helper column to indicate whether each row is filtered or not, e.g. in Sheet1 Z2 use this formula copied down

=SUBTOTAL(3,A2)

Now you can use a COUNTIFS function to count visible rows containing "Apple"

=COUNTIFS(A:A,"*Apple*",Z:Z,1)

barry houdini

Posted 2013-12-02T13:10:24.530

Reputation: 10 434

Finally something that works...thank you sir! However as you mentioned...it does slow down the process considerably. And I have to reference the whole column as the last row number will always vary. Is there no way to speed things up? – Gh0sT – 2013-12-02T18:52:00.390

How much data do you have - you might consider using a dynamic named range....or use the helper column approach - that should be considerably quicker – barry houdini – 2013-12-02T18:58:45.423

~2000 rows...I will consider the helper column. btw can you please explain dynamic named range. – Gh0sT – 2013-12-02T19:00:27.107

A dynamic named range expands automatically as you add data, so you don't have to reference the whole column but the formula will update automatically, so you create a DNR called data, for example and use that in your long formula in place of Sheet1!A:A - see here http://www.contextures.com/xlNames01.html#Dynamic

– barry houdini – 2013-12-02T19:05:36.643

...yet another way would be to convert your data to a table (using INSERT > Table) - tables work a little like DNR's without you having to define the named range - you can refer to a column of the table and that will exopand automatically as you add data – barry houdini – 2013-12-02T19:07:51.293

This sounds good...I'll try it out and see if it helps...thanks a lot for your help! – Gh0sT – 2013-12-02T19:15:37.953

Test: =COUNTIF(A2:A10;"Apple*") change range

STTR

Posted 2013-12-02T13:10:24.530

Reputation: 6 180

What I need is the count of all visible cells containing the word "Apple". Note that this should exclude the hidden (filtered out) cells. – Gh0sT – 2013-12-02T13:38:12.827

1STTR beat me to it :D @Gh0sT, you should be able to replace (Sheet1!A:A="<Text to search>") with COUNTIF(A:A,"*apple*") in the function you already have. You'll need to use *apple* rather than Apple*, otherwise it'll only count cells that START with Apple – CLockeWork – 2013-12-02T13:47:17.307

I did as you said..replaced (Sheet1!A:A="<Text to search>") with COUNTIF(A:A,"*apple*") but it's showing some garbage value. – Gh0sT – 2013-12-02T16:42:55.400