COUNTA with FILTER function returning 1 if no results are found

Justplainj

New Member
Joined
Apr 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
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 IDColumn R - DepartmentColumn V - Start DateColumn W - End Date
ID001HR1 jan 2020
ID002HR1 jan 2020
ID003HR1 feb 202030 aug 2021
ID004Finance1 mar 2020
ID005FInance1 jun 202031 nov 2021
ID006GoMo1 jun 2020
ID007GoMo1 jul 2020
ID008GoMo1 aug 2020
ID009Call Centre1 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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:

Excel Formula:
=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))
 
Upvote 0
Solution
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,
 
Upvote 0
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:

Excel Formula:
=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))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top