Forum Discussion
Make FILTER() return empty
I'm attempting to find the amount of rows that fulfil two conditions, in order to filter for these conditions in Tabel4 I'm using a FILTER() function. The problem is that I cannot get it to return 0, because any string or number counts as a row.
=ROWS(FILTER(Tabel4;(U2:U791=TRUE)*(K2:K791=*variable*);""))
One solution I've found is forcing an error through NA(), and catching that instead.
=IFNA(ROWS(FILTER(Tabel4;(U2:U791=TRUE)*(K2:K791=8283);NA()));0)
I can't help but feel there should be a better way to do this though, is there?
Levi_Porton The FILTER function is not well suited for counting records (ROWS) because empty arrays are not supported, which means a workaround is required to return 0 when no records are found. Another approach is to simply use the SUM function (or SUMPRODUCT) with the conditions you've already written. For example:
=SUM((U2:U791=TRUE)*(K2:K791=8283))Or just use the COUNTIFS function:
=COUNTIFS(U2:U791; TRUE; K2:K791; 8283)Cheers!
2 Replies
Levi_Porton The FILTER function is not well suited for counting records (ROWS) because empty arrays are not supported, which means a workaround is required to return 0 when no records are found. Another approach is to simply use the SUM function (or SUMPRODUCT) with the conditions you've already written. For example:
=SUM((U2:U791=TRUE)*(K2:K791=8283))Or just use the COUNTIFS function:
=COUNTIFS(U2:U791; TRUE; K2:K791; 8283)Cheers!
- This helps you un understanding the <a href="https://toothchartinfo.com/quadrants/">tooth quadrants</a>.
