Forum Discussion

Levi_Porton's avatar
Levi_Porton
Copper Contributor
Dec 12, 2023
Solved

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,...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Dec 13, 2023

    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!

djclements's avatar
djclements
Silver Contributor
Dec 13, 2023

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!

Resources