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

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
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()))
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,605
Office Version
  1. 365
Platform
  1. Windows
I think you might mean SecondDose,"<="&TODAY()
 
Upvote 0

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
1640299070812.png
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Can you post sample data rather than an image?
 
Upvote 0

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,496
Office Version
  1. 365
Platform
  1. Windows
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"
 
Last edited:
Upvote 0

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
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))
 
Upvote 0

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,191,416
Messages
5,986,434
Members
440,030
Latest member
ninomato

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
Top