I have a long filtered list of numbers next to names. I am trying to count the zeros as I change the filter. Unfortunately COUNTIF includes cells that have been excluded by my filter.  How can I count the zeros in my Numbers column only when they are not excluded by the filter?
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="&lt;Text to search&gt;"))`   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.
Close Vote Review Audits are too obvious The other day, I got an obviously good question in a Close Vote Review.  Even if I didn't read the question,  is a bit of a giveaway.  Today I got one where   Also, on sites other than SO (where I understand the queues are huge), the number of reviews needed is often low enough (single digits) that you usually notice when you get an extra one.  I don't mind - I'm quite happy to get easy-to-pass audits - but maybe they could be a little harder... Proof that every number ≥ $8$ can be represented by a sum of fives and threes. Can you check if my proof is right?  Theorem.  $\forall x\geq8, x$ can be represented by $5a + 3b$ where  $a,b \in \mathbb{N}$.  Base case(s): $x=8 = 3\cdot1 + 5\cdot1 \quad \checkmark\\ x=9 = 3\cdot3 + 5\cdot0 \quad \checkmark\\ x=10 = 3\cdot0 + 5\cdot2 \quad \checkmark$  Inductive step:  $n \in \mathbb{N}\\a_1 = 8, a_n = a_1 + (x-1)\cdot3\\ b_1 = 9, b_n = b_1 + (x-1)\cdot3 = a_1 +1 + (x-1) \cdot 3\\ c_1 = 10, c_n = c_1 + (x-1)\cdot3 = b_1 + 1 + (x-1) \cdot 3 = a_1 + 2 + (x-1) \cdot 3\\ \\ S = \{x\in\mathbb{N}: x \in a_{x} \lor x \in b_{x} \lor x \in c_{x}\}$  Basis stays true, because $8,9,10 \in S$  Lets assume that $x \in S$. That means $x \in a_{n} \lor x \in b_{n} \lor x \in c_{n}$.  If $x \in a_n$ then $x+1 \in b_x$,  If $x \in b_x$ then $x+1 \in c_x$,  If $x \in c_x$ then $x+1 \in a_x$.  I can't prove that but it's obvious. What do you think about this? How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue? Ubuntu 18.04 not detecting full RAM size What is this agglomeration of pink cells I found attached to a stone? Software center not loading software lists Subject–verb agreement not clear in "Is two bars OK?" Is the following correct?     Can I get some chocolate for myself? Yes, you can but not much.   Is two bars OK?   Is there any rule to explain the sentence above?  The dialogue's taken from Grammar Textbook Round-Up Level #3. Prove that any complex matrix can be expressed in term of Hermitian and anti-Hermitian matrices How can we prove that any complex matrix can be expressed in term of hermitian and anti-hermitian matrices of the following form $A = H_1 + iH_2$ Where, $H_1$ is hermitian and $H_2$ is an anti-hermitian matrix why does files (nautilus) open a new window although there's one already open? When I click on "files" in the launcher it opens another window although the normal action is to maximize the window already open. It doesn't always do that. Is it a bug? How to harden an SSH server? Firstly, how do you gain the score exactly? I know it shows in the bar on the side of the map, but I remember a match where I was lone-wolfing and still got a pretty high score in that bar. I suppose my other squad members were together, but do I also gain for their work?  Also, does the score reset when I start a new game? I mean, I play support with the Offensive perk and it took me a long game of conquest to just unlock lvl-3 extra grenade. In the next match, that 'nade is gone. Don't I keep it forever? I stumbled across this relationship while I was messing around. What's the proof, and how do I understand it intuitively? It doesn't really make sense to me that the sum of odd numbers up to $2x + 1$ should equal $x^2$. Why do we use decibels instead of just using intensity to measure how loud things are? Unparsed HTML shows on search result page When you search on any of the SE network sites, with a tag (so that a description is shown); it shows the tag info content box, with the highlight-search-term span elements.  For eg. I searched ; ; and the results are as shown:                  Notice the &lt;span class='search-highlight'&gt; boxes around searched tag terms. Suppose $f$ and $g$ are entire functions, and $|f(z)| \leq |g(z)|$ for all $z \in \mathbb{C}$, Prove that $f(z)=cg(z)$. Suppose $f$ and $g$ are entire functions, and $|f(z)| \leq |g(z)|$ for all $z \in \mathbb{C}$, Prove that $f(z)=cg(z)$.   My try :  I consider $h(z)=\frac{f(z)}{g(z)}$. If I prove that $h(z)$ is entire, using the fact that $|h(z)|\leq 1$, the result follows immediately from Liouville's theorem.  To prove that $h(z)$ is entire, I have to prove that $h(z)$ has removable singularities at possible zeros of $g(z)$.  Suppose $g(z_0)=0 \Rightarrow |g(z_0)|=0 \Rightarrow |f(z_0)|=0$, BUT what if $z_0$ be a root of of order $k$ of $g(z)$ (i.e. $f^{(n)}(z_0) = 0 $ for all $ 0 \leq n \leq k-1$) and be a zero of order $m$ of $f(z)$ and $k&gt;m$ ?  And if that's not the case, how to rigorously show that $z_0$ is a removable singularity ?  Thank you all in advance. Immutability, Association and typing This is a reformulation of a deleted question:  If  $a_1 &gt; 0$ and $a_2 &gt; 0$ and $2a_{n+2} \le a_{n+1}+a_n$,  show that  $\lim \sup a_n \le \frac23 a_2 + \frac13 a_1$.  My proof involves showing that $a_{n+2} \le u_{n} a_{n+1} + (1-u_n)a_n $ where $u_n \to \frac23$, and I wondered if there is a simpler proof.  I will post my proof in a couple of days if no one does. Fixed page header overlaps in-page anchors What's the difference between centre of mass & centre of gravity for massive bodies? My book says:     For most of the small objects, both are same. But for mammoth ones, they are really different ones. And in a gravity-less environment, COG is absent; COM still exists.   Ok, what's the big deal when things are small and big? How these two :  &amp; ? Floor function in complex plane My question is very simple. How would one take the floor of a complex number and what is the floor of i?