COUNTA with FILTER function returning 1 if no results are found

[Justplainj]

Hi All,

I am sure someone will be able to assist me.

I am using the formula below to count non-blank cells (cells contains text) to a ranged that is filtered using the filter function.
Column C is the employee ID being counted, Column R is the department and column V & W is the start date and end date columns for the employees and AH2 is the date
=COUNTA(FILTER($C$1:$C$2494,($R$1:$R$2494="GoMo")*((($W$1:$W$2494 = "")+($W$1:$W$2494 >AH2))*($V$1:$V$2494 <AH2)),""))

The trouble I am having is when the filter function returns no result it defaults to 1 because the blank value between the quotation marks is for some reason not seen as an actual blank by the COUNTA function.
The end result when the filter function has no result should be for the count to be 0.

If the filter function does find results, the formula works perfectly.

Example table below

Column C - Emp ID	Column R - Department	Column V - Start Date	Column W - End Date
ID001	HR	1 jan 2020
ID002	HR	1 jan 2020
ID003	HR	1 feb 2020	30 aug 2021
ID004	Finance	1 mar 2020
ID005	FInance	1 jun 2020	31 nov 2021
ID006	GoMo	1 jun 2020
ID007	GoMo	1 jul 2020
ID008	GoMo	1 aug 2020
ID009	Call Centre	1 nov 2020

If AH2 is 30 Sep 2021 then the formula would result in 2 because there are 2 people in that department.
But if AH2 is 31 Mar 2020 then the formula results in 1 instead of 0.

Thank you,
Jaco

 

[RoryA]

If your filter returns no records, then the return value is an empty string, which COUNTA will count, hence 1. You could do something like:

=LET(a,FILTER($C$1:$C$2494,($R$1:$R$2494="GoMo")*((($W$1:$W$2494 = "")+($W$1:$W$2494 >AH2))*($V$1:$V$2494 <AH2)),""),INDEX(IF(a="",0,counta(a)),1))

 

[Justplainj]

Hi RoryA.

Thank you for this.
I honestly do not know how that formula works (never used the LET function) but it works perfectly.

I will go study out the LET function.

Solution Marked

Thank you,

 

[RoryA]

Let basically allows you to store the results of a function in a variable so you don't have to keep repeating the same function. Without it, the formula would look like:

=INDEX(IF(FILTER($C$1:$C$2494,($R$1:$R$2494="GoMo")*((($W$1:$W$2494 = "")+($W$1:$W$2494 >AH2))*($V$1:$V$2494 <AH2)),"")="",0,counta(FILTER($C$1:$C$2494,($R$1:$R$2494="GoMo")*((($W$1:$W$2494 = "")+($W$1:$W$2494 >AH2))*($V$1:$V$2494 <AH2)),""))),1))