7

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.

Improve this question
2
  • Do you expect 3 as the result??
    – Gary's Student
    Dec 2, 2013 at 16:29
  • In this case...yes.
    – Gh0sT
    Dec 2, 2013 at 16:44

2 Answers 2

Reset to default
6

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)

Improve this answer
7
  • 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
    Dec 2, 2013 at 18:52
  • 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
    Dec 2, 2013 at 18:58
  • ~2000 rows...I will consider the helper column. btw can you please explain dynamic named range.
    – Gh0sT
    Dec 2, 2013 at 19:00
  • 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 contextures.com/xlNames01.html#Dynamic
    – barry houdini
    Dec 2, 2013 at 19:05
  • ...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
    Dec 2, 2013 at 19:07
0

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

Improve this answer
3
  • 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
    Dec 2, 2013 at 13:38
  • 1
    STTR 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
    Dec 2, 2013 at 13:47
  • 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
    Dec 2, 2013 at 16:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .