CODEWITHSUNDEEP
excelexcel-formula
Chrismas007
Chrismas007

Reputation: 6105

CountIf With Filtered Data

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.

Upvotes: 0

Views: 13327

Answers (3)

Scott Craner
Scott Craner

Reputation: 152660

With Office 365 we can finally get rid of the volatile OFFSET by using BYROW instead:

=SUMPRODUCT(BYROW(B2:B18,LAMBDA(a,SUBTOTAL(3,a))),ISNUMBER(SEARCH("Pear",B2:B18))+0)

The BYROW(B2:B18,LAMBDA(a,SUBTOTAL(3,a))) does the same as SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)) without being volatile.

Upvotes: 3

Jay
Jay

Reputation: 65

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

B861 is what ever cell you are referencing.

Upvotes: -1

Chrismas007
Chrismas007

Reputation: 6105

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.

Upvotes: 4

Related Questions