COUNTIFS, WITHIN DATE RANGE, ISBLANK FOR ADDITIONAL DATE RANGE
I need to write a formula for counting active projects that are past due for a current month based on schedule complete date being in that current month and when actual complete date is blank.
Project Status = Active
Schedule Complete Date = 7.1.20 - 7.31.20
Actual Complete Date = Blank
Here is the data and fields I would be using. So in this screen shot the result would return 1 for July 2020 since the scheduled complete date was 7.6.20 but the project doesn't have an actual complete date.
Best Answer
-
Happy to help. 👍️
Answers
-
Try something like this...
=COUNTIFS([Project Status]:[Project Status], "Active", [Scheduled Complete Date]:[Scheduled Complete Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), [Actual Complete Date]:[Actual Complete Date], @cell = "")
-
Paul,
For the Month(@cell) do I need to put the # that represents the month, like 7 for July or do I always leave it as 0?
-
Leave it exactly as is.
-
Well it returns 4 and for July there is only 2 so it is not calculating something correctly. I need it look at a specific date range for a month based on scheduled complete date being in that month. So my data shows there are 4 for the year but only two in the month of July so somehow I need to add specific data ranges based on scheduled complete date.
So only count the # of active projects that do not have an actual complete date that were scheduled to complete between July 1st to July 31st. Your formula works great for a total just not specific months.
-
Can you provide a screenshot of the data you are evaluating? My formula does specify a month, so it should be working.
Your post asked about "current month". If you are wanting to calculate for July (last month), then you will need to replace the TODAY functions with the appropriate month and year numbers. I only used today because of your "current month" requirement.
-
And the EXACT formula you are using... Can you copy/paste that from the sheet to here?
-
=COUNTIFS({SM Demand Management Range 13}, "Active", {SM Demand Management Range 14}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {SM Demand Management Range 15}, @cell = "")
SM Demand Management Range 13 = Status
SM Demand Management Range 14 = Scheduled Complete Date
SM Demand Management Range 15 = Actual Complete Date
-
Ok. If you are wanting to count for the month of July, then you will need to replace those portions with the appropriate month and year as I suggested above. I only used the TODAY function because your original post said "current month".
Current Month Formula:
=COUNTIFS({SM Demand Management Range 13}, "Active", {SM Demand Management Range 14}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {SM Demand Management Range 15}, @cell = "")
July 2020 Formula:
=COUNTIFS({SM Demand Management Range 13}, "Active", {SM Demand Management Range 14}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2020), {SM Demand Management Range 15}, @cell = "")
-
So would it look like this:
=COUNTIFS({SM Demand Management Range 13}, "Active", {SM Demand Management Range 14}, AND(IFERROR(MONTH(@cell), 0) = MONTH(@cell), 7), IFERROR(YEAR(@cell), 0) = YEAR(@cell), 2020), {SM Demand Management Range 15}, @cell = "")
-
No. It would look exactly like the second formula above.
July 2020 Formula:
=COUNTIFS({SM Demand Management Range 13}, "Active", {SM Demand Management Range 14}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2020), {SM Demand Management Range 15}, @cell = "")
-
Thanks, that worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!