Search code examples
excelfilterexcel-formulaoffice365formula

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

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?

Solution

  • 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)