How to display zero in place of error in filter formula in google spreadsheet
=IFERROR(SUMPRODUCT(filter(Sheet1!$A$1:$A$401, (Sheet1!$B$1:$B$401>= E1) * (Sheet1!$B$1:$B$401<= E2))),0)
I've had the same issue with my sheets too. I agree with the comments above hiding the error isn't best practice. However hiding the N/A response / customising it is very useful! You need to wrap the whole logic in a NA statement so it starts with it first. Here is how you can do something like this with the formula you are using.
=IFNA(SUMPRODUCT(FILTER((Sheet1!$A$1:$A$401,
(Sheet1!$B$1:$B$401>= E1)*(Sheet1!$B$1:$B$401<= E2))),
"input text or response you want here")
Change "input text or response you want here" to what you want to respond. If you just want it to return a numerical value, like 0
, you can remove the quotes at the end and the text response and just put the number. Don't forget the comma (,
) after the bracket though!
It should look like this:
=IFNA(SUMPRODUCT(FILTER((Sheet1!$A$1:$A$401,
(Sheet1!$B$1:$B$401>= E1)*(Sheet1!$B$1:$B$401<= E2))),0)
I do NOT recommend IFERROR because it will suppress all type of errors. And that is not a good practice.
What you should be doing is...
IF the filter returns 0 items, THEN just display 0
ELSE, display sum of all the items.
=IF(COUNT(FILTER(_range_,_condition_)) = 0, 0, SUM(FILTER(_range_,_condition_)))