Countifs using greater than a date (as well as other criteria)

[rsboulevard]

I am trying to count the # of dates that are older than today along with other criteria in other columns. I am getting a value! error. The first formula returns an accurate count, but when I mix in the the date count criteria, I receive the error message. SecondDose is a named range of cells that only contain short date type dates, no blank cells.

=SUM(COUNTIFS(Employees!G11:G9958,"Fully Vaccinated",Employees!D11:D9958,"Active",Employees!J11:J9958,{"Pfizer-BioNTech","Moderna"}))

=SUM(COUNTIFS(Employees!G11:G9958,"Fully Vaccinated",Employees!D11:D9958,"Active",Employees!J11:J9958,{"Pfizer-BioNTech","Moderna"},SecondDose,">="&TODAY()))

 

[DRSteele]

I think you might mean SecondDose,"<="&TODAY()

 

[rsboulevard]

 

[rsboulevard]

I think you might mean SecondDose,"<="&TODAY()

Yes it is...but I still get the error.

 

[Norie]

Can you post sample data rather than an image?

 

[Alex Blakenburg]

I don't think that randomly throwing in a named range is a good idea.
What is the range address of SecondDose, is it a single column and does it run from row 11:9958 ?
Your data looks to be in a table so Table References for all columns would be preferable.

If the issue is not flushed out by the above, then as per Norie's request can you provide an XL2BB of some sample data, together with the range address of SecondDose and the name of the Table you are using and whether your formula is on Sheet "Employees"

 

[rsboulevard]

Took out the SecondDose range and it worked.

=SUM(COUNTIFS(Employees!G11:G1800,"Fully Vaccinated",Employees!D11:D1800,"Active",Employees!J11:J1800,{"Pfizer-BioNTech","Moderna"},Employees!L11:L1800,"<="&TODAY()-180))

 

[rsboulevard]

Took out the SecondDose range and it worked.

=SUM(COUNTIFS(Employees!G11:G1800,"Fully Vaccinated",Employees!D11:D1800,"Active",Employees!J11:J1800,{"Pfizer-BioNTech","Moderna"},Employees!L11:L1800,"<="&TODAY()-180))

Thank you for your help everyone.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.