COUNTIFS, WITHIN DATE RANGE, ISBLANK FOR ADDITIONAL DATE RANGE

Corey Fletcher

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.

Paul Newcome

Happy to help. 👍️

Paul Newcome

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 = "")

Corey Fletcher

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?

Paul Newcome

Leave it exactly as is.

Corey Fletcher

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.

Paul Newcome

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.

Corey Fletcher

Paul Newcome

And the EXACT formula you are using... Can you copy/paste that from the sheet to here?

Corey Fletcher

=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

Paul Newcome

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 = "")

Corey Fletcher

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 = "")

Paul Newcome

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 = "")

Corey Fletcher

Thanks, that worked!

Paul Newcome

Happy to help. 👍️