Counta + Filter function on Google spreadsheet cannot give me a Zero

4
votes

I am trying to use FILTER and COUNTA in Google spreadsheet. Spreadsheet

The formula in E1 is =COUNTA(filter($A$1:$A$12,$A$1:$A$12>=$C1,$A$1:$A$12<=$D1))

This formula help me to filter and count the date (Column A) which is within the date range (Column C and D).

The result in E1 and E2 is correct. However, E3, E4, E5 do not give me a zero, as there is no date fit in the range.

Anyone can help me on this to make it return a Zero if there is no date fit in the date range?

1
google-sheets

1 Answers

9
votes

Martin. The function COUNTA is designed to count ALL values in a dataset, including repeats, zero-length strings, whitespace, and even "#N/A"s. So, you must wrap your FILTER formula in IFERROR, thus resulting in a truly empty cell where there is no date range match.

=COUNTA( IFERROR( filter($A$1:$A$12,$A$1:$A$12>=$C1,$A$1:$A$12<=$D1)))

You can read more in the Google Sheets documentation on COUNTA.