# Countif- Visible Cells in Filter mode

#### aabbasi

##### Board Regular
Hi:

Could someone please let me know the solution for the following problem:

I have values in cell B2:B50 with values such as Quality, Eng, Purchasing etc. When i go in Auto filter based on Coulmn A2:A50 (with July only) and count "Quality" manually under B2:B50, the answer is 26. But if I write formula Countif(B2:B50, "Quality") I get answer 41.

Is there a way to use Countif function, if I am in the Autofilter mode so as it counts only that rows which are visible under Autofilter and not ALL rows.

Thank you

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Ekim

##### Well-known Member
No. of Filtered Records: =SUBTOTAL(3,\$B\$2:\$B\$50)
No. of Total Records: =COUNTA(\$B\$2:\$B\$50)

Something more exotic (recently seen on the Net – original author not recorded):

Code:
``=IF(SUBTOTAL(3,B2:B50)<COUNTA(B2:B50), "Filtered -- " &SUBTOTAL(3,B2:B50)& " of " &COUNTA(B2:B50)&" records","")``
Produces:
Filtered -- 4 of 15 records
(or whatever)

HTH

Mike

#### aabbasi

##### Board Regular

However, I like to Subtotal based on "Criteria". My criteria is to subtotal all records that have value "Quality" with Auto Filter on.

Your formula does not puts Criteria in equation. Any way appreciate your help

#### Ekim

##### Well-known Member
However, I like to Subtotal based on "Criteria". My criteria is to subtotal all records that have value "Quality" with Auto Filter on.
See the simulation.
Filter - totals and count of filtered records.xls
ABCD
1Data1Data2Data3Data4
2A735672201
3B226111473
4A73457209
5A92257632
6C130500976
7B401278790
8B76982519
9A854106491
10C584697111
11C509343963
12D991652863
13D115253121
14A50562940
15B447828568
167,4675,1987,857
Sheet1

Using autofilter - If I filter on B, the first formula will show 4. If filtered on C, the formula will show 3 etc. The formula will count the visible rows. You don't need to have the criteria as part of the formula.

Regards,

Mike

##### MrExcel MVP

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality"))

#### aabbasi

##### Board Regular

Thank you both of you for a wonderful solution. It works for me.

Have a wonderful day!

best regards,
Abbasi

#### mgirvin

##### Well-known Member

Amazing!

Can you walk me through the logic of how this formula is working?

In particularly, how is the SUBTOTAL and OFFSET constructed finding only the Visible Rows?

##### MrExcel MVP

Amazing!

Can you walk me through the logic of how this formula is working?

In particularly, how is the SUBTOTAL and OFFSET constructed finding only the Visible Rows?
It's SUBTOTAL that is sensitive to visible cells. The function num 3 (i.e., COUNTA) yields 1 if any data in a given visible cell, otherwise 0 along with all non-visible cells.

Suppose we have it for a shorter range:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-MIN(ROW(B2:B7)),,1))*(B2:B7="Quality"))

Evaluationg just the row bit we get:

SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,{0;1;2;3;4;5},,1))*(B2:B7="Quality"))

SubTOTAL gets applied successively on

OFFSET(B2,0,,1)
OFFSET(B2,1,,1)
OFFSET(B2,2,,1)

and so on... leading to something like:

SUMPRODUCT({0;0;1;0;0;1}*(B2:B7="Quality"))

Cells with 1 do house data. If the data housed in the corresponding range is equal to "Quality", that cell is counting in.

#### mgirvin

##### Well-known Member

Thanks for posting an answer 5 years after the last post!

OK, So SUBTOTAL is the function that is sensitive to visible cells. But why the 3 instead of the 103 for the first argument of SUBTOTAL? I thought that the 3 “includes hidden values” (not visible values) and 103 “ignores hidden values”. Or is there a difference between what Excel thinks of as hidden and visible? I just did a test and it appears that this is the case: hidden is when you hide a row or column, visible is when you filter.

So is it true that SUBTOTAL with a 3 as the first argument and SUBTOTAL with a 103 as the first argument will do the same thing when the table is filtered and some values are not “visible”? But they will calculate different results when rows are hidden?

##### MrExcel MVP
3 and 103 operate similarly for the autofiltered lists/ranges. The latter works additionally with rows which are manually hidden.

1,106,625
Messages
5,512,472
Members
408,899
Latest member
cve60069