CODEWITHSUNDEEP
excelexcel-formula
David Doria
David Doria

Reputation: 10273

Count rows in a dynamic range

Consider a very simple example of a dynamic range: enter image description here

Cell C10 (arbitrary cell):

=FILTER(Table1[List1],NOT(ISNUMBER(XMATCH(Table1[List1],Table1[List2]))))

I would like to now know how many rows are in the dynamic range starting at C10. I tried simply:

=ROWS(C10)

but it returns '1' even when there are more than one rows returned. Is this possible?

Upvotes: 0

Views: 2235

Answers (1)

BigBen
BigBen

Reputation: 50162

Use COUNTA and the spilled range operator #:

In C10:

=FILTER(Table1[List1],NOT(ISNUMBER(XMATCH(Table1[List1],Table1[List2]))))

In D10:

=COUNTA(C10#)

enter image description here

Or like your original approach, you can use ROWS with the spilled range operator:

=ROWS(C10#)

Upvotes: 1

Related Questions