Countif- Visible Cells in Filter mode

aabbasi

Board Regular
Joined
Mar 4, 2002
Messages
188
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 :(
 

Some videos you may like

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
Joined
Jun 30, 2002
Messages
1,416
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
Joined
Mar 4, 2002
Messages
188
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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

aabbasi

Board Regular
Joined
Mar 4, 2002
Messages
188
Mike and Aladin:

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

Have a wonderful day!

best regards,
Abbasi :)
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
3 and 103 operate similarly for the autofiltered lists/ranges. The latter works additionally with rows which are manually hidden.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top