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

drom

Active Member
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
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
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:

MrExcel MVP
If you are applying autfilter on column A...

=SUBTOTAL(3,A2:A50)

will deliver the count of filtered rows.

drom

Active Member
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:

MrExcel MVP
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<>""))

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

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...