Countif- Visible Cells in Filter mode

[aabbasi]

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

 

[Ekim]

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):

=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]

Thank you for your response.

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]

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
	A	B	C	D
1	Data1	Data2	Data3	Data4
2	A	735	672	201
3	B	226	111	473
4	A	734	57	209
5	A	922	57	632
6	C	130	500	976
7	B	401	278	790
8	B	769	82	519
9	A	854	106	491
10	C	584	697	111
11	C	509	343	963
12	D	991	652	863
13	D	115	253	121
14	A	50	562	940
15	B	447	828	568
16		7,467	5,198	7,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

 

[Aladin Akyurek]

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

 

[aabbasi]

Mike and Aladin:

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

Have a wonderful day!

best regards,
Abbasi

 

[mgirvin]

Dear Aladin,

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?

 

[Aladin Akyurek]

Dear Aladin,

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]

Dear Aladin,

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?

 

[Aladin Akyurek]

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