Count Visible rows, no matter if are blank ot not blank in a table

drom

Active Member
Joined
Mar 20, 2005
Messages
400
Sa I have a Table (eg: A1:B50)
Imaging my Column A:B'cells are most of the time empty

If I filter by Column(A, to show blanks and = to France, I count 15 rows
If I filter by France I only count 10

But any formula to get 15 ??

I do not care about France, I would like to Count How many rows I have filtered, no matter if they are empty, blank or with info

ANy idea

I was trying to use:

=SUMPRODUCT((A2:A50="")*SUBTOTAL(103;OFFSET(A2;ROW(A2:A50)-MIN(ROW(A2:A50));0)))+SUMPRODUCT((A2:A50<>"")*SUBTOTAL(103;OFFSET(A2;ROW(A2:A50)-MIN(ROW(A2:A50));0)))

BUT DOES NOT WORK
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,651
Office Version
365
Platform
Windows
Add column C to your table and inset a value (** see below) in EVERY row 2:50 , and use this formula
=SUBTOTAL(3,C2:C50)

If your table includes a column which contains a value in every row, then simply use that column

** a value
any value - the cell must not be empty
space
apostrophe
number
word
the value can differ between rows
 
Last edited:

drom

Active Member
Joined
Mar 20, 2005
Messages
400
I do not want to use, additional columns.
I would like to know if I have any other option
=ROWS(A2:A50) counts hidden and not hidden rows

=SUMPRODUCT((A2:A50<>"")*SUBTOTAL(103;OFFSET(a2;ROW(A2:A50)-MIN(ROW(A2:A50));0))) counts visible cells with column A non Blank
=SUMPRODUCT((A2:A50="")*SUBTOTAL(103;OFFSET(a2;ROW(A2:A50)-MIN(ROW(A2:A50));0))) does not count blank visible cells,
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
If you are applying autfilter on column A...

=SUBTOTAL(3,A2:A50)

will deliver the count of filtered rows.
 

drom

Active Member
Joined
Mar 20, 2005
Messages
400
Sorry but
=SUBTOTAL(3,A2:A50) counts filtered cells in A2:A50 different than BLANK, <> ""

I need to count filtered cells in A2:A50 no matter if the cell is blank or if the cell contains Info
Actually I am not going to filter the Column A, I am going to filter the Column X, and in my table I do not have any column always with data, and I do not want to add any additional column, like =row(), or rand() or..
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Sorry but
=SUBTOTAL(3,A2:A50) counts filtered cells in A2:A50 different than BLANK, <> ""

I need to count filtered cells in A2:A50 no matter if the cell is blank or if the cell contains Info
Actually I am not going to filter the Column A, I am going to filter the Column X, and in my table I do not have any column always with data, and I do not want to add any additional column, like =row(), or rand() or..
Try...

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$50)-ROW($A$2),0)),($A$2:$A$50="")+($A$2:$A$50<>""))
 

Forum statistics

Threads
1,081,849
Messages
5,361,681
Members
400,645
Latest member
Zeak

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top