CountA on a Filter Formula return 1 instead of 0

[ikimashu]

To start with, I am using Office 365 Excel version.

I am using a Filter formula to extract information I need, then I would add CountA at the front to give me a count of filtered result. It worked fine, until I found when the filtered result was 0 (Zero), the CountA would still return 1 (one).

Can experts advise what is a solution to this.

Sample of formula:

1- =FILTER(Tracking[Name],(Tracking[Date]>=$Q$4)*(Tracking[Date]<=EOMONTH($Q$4,0))*(Tracking[Type]=Ref!F2),"")
Result: Empty

2- =COUNTA(FILTER(Tracking[Name],(Tracking[Date]>=$Q$4)*(Tracking[Date]<=EOMONTH($Q$4,0))*(Tracking[Type]=Ref!F2),"")
Result: 1

I would like to have that COUNTA to return 0, but just couldn't find a way around.

Thanks!

Tony

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

=IFERROR(ROWS(FILTER(Tracking[Name],(Tracking[Date]>=$Q$4)*(Tracking[Date]<=EOMONTH($Q$4,0))*(Tracking[Type]=Ref!F2))),"")

 

[jasonb75]

You need to catch the CALC error from the empty filter with the ROWS function. COUNTA will still count the nullstring value if empty or the error, whichever occurs.

=IFERROR(ROWS(FILTER(Tracking[Name],(Tracking[Date]>=$Q$4)*(Tracking[Date]<=EOMONTH($Q$4,0))*(Tracking[Type]=Ref!F2))),0)

 

[ikimashu]

Solved! Thank you very much for the help!
@Fluff @jasonb75

 

[Fluff]

Glad we could help & thanks for the feedback.