Help with formula to count number of occurances of texts from visible range

[rv02]

Hello All

Can someone please help me with the following formula

I have a formula which counts the number of occurrences of a certain text from a range (regardless of the upper or lower case) in a table.

What I need to use subtotal function or anything else to only count the number of occurrences from a visible range so when a filter is applied to the table it only count the number of occurrences for the visible cells.
my formula is

Please Login or Register  to view this content.

when I put subtotal(103,......) I get an error

any help is appreciate it

[Jonmo1]

Try a helper column, say column E
E1 and Filled down put =SUBTOTAL(103,D1)

Then use
=SUMPRODUCT((LEN(Comments!$D:$D)-LEN(SUBSTITUTE((UPPER(Comments!$D:$D)),UPPER(A2),"")))/LEN(A2),$E:$E)

[Jonmo1]

However, Might I suggest the simpler COUNTIF function

=COUNTIF(Comments!$D:$D,"*"&A2&"*")

Then to do just the visible cells only, use the same subtotal in column E as in previous post
And use
=COUNTIFS(Comments!$D:$D,"*"&A2&"*",Comments!$E:$E,1)



FYI, I highly recommend NOT using entire column references like D:D in sumproduct.

[rv02]

Dear Jonmo

Thank you so much for your help, both ideas worked brilliantly, and I have used a range rather than column and it is much quicker.

I was wondering whether there is a way to do this without the helper column, as I export the table by VB code into the Comments sheet and the formula is in different sheet. If not that's not a problem as I have to somehow write a vb code to automatically insert the helper column . Reason is when I refresh the data the code deletes the table and inserts a new one (exactly the same dimensions) .

However your solution works fantastically and I just add a code for the extra column.

Tanks again for your help

[Jonmo1]

You're welcome.

I know it 'Can' be done in one formula, I'm sure there are some other formula guru's that can do it.
I just prefer to use the helper column method (personal choice)
Because I figure once you have 1 formula that you need to ignore hidden rows, you will likely have another and another...
You would have to repeat that subtotal work in each formula.
So putting the subtotal work in each formula itself, that is creating alot of unnecessary work = poor performance of your sheet.

With the helper column, you only do the subtotal work once.
Then all of your other formulas that need to ignore hidden rows can each refer to that same helper column.

[Tony Valko]

I was wondering whether there is a way to do this without the helper column

Can you post a SMALL sample file that shows us what result you expect?

No more than 20 rows and as few columns worth of data is plenty. We don't want to have to sift through tons of irrelevant data looking for the solution!