excel - CountIf With Filtered Data

Question

I was researching a way to count the number of zeroes in a column of data, even if the data gets filtered. I found the following solution:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0)

Where, B2:B18 is the total list of data and "Pear" is the criteria being counted.

• Can someone explain how this formula is accomplishing this task?
• Is there a simpler way of doing this?

Answer 1

I was able to determine that:

SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1))

is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden.

ISNUMBER(SEARCH("Pear",B2:B18))+0)

is used to return an array of which cells contain "Pear". If "Pear" is found, 1 is returned, else 0.

SUMPRODUCT(arrayofvisiblecells , arrayofcellswithPear)

is used to sum all of the times when the cell is visible AND "Pear" is present. 1*1 else you will be multiplying by a 0.