How to identify rows of FILTER() when the result is 0?-CodePudding

Dear Excel masters please take a look. I have a nested function like this:

=ROWS(FILTER(Table,Table[Col.A]="d",0))

Here's the table:

| Col.A | Col.B | Col.C |
|-------|-------|-------|
|   a   |   1   |   6   |
|   b   |   2   |   7   |
|   a   |   3   |   8   |
|   b   |   4   |   9   |
|   c   |   5   |   0   |

The inner function FILTER returns 0 since there's no "d" in Col.A, but ROWS(0) returns 1 not 0. Is there any solution or replacement that can make it return the correct result 0?

CodePudding user response：

If you omit the 0 from the FILTER-Formula, you will receive an error - which you can use to return 0 for the rows count:

=IFERROR(ROWS(FILTER(Table,Table[Col.A]="d")),0)

CodePudding user response：

If I wanted to be really pedantic and avoid Iferror (in case it masked a different error) I would use

=LET(filter,FILTER(Table,Table[Col.A]=E3,0),IF(@filter=0,0,ROWS(filter)))

Why is the @ necessary? Because otherwise it would check the filtered data cell by cell and you would get this:

where the filtered cells are replaced by rows() except where they contain zero.