count the number of tasks due within 30 days

System

This discussion was created from comments split from: Formula Woes.

bentlb3

Paul, I'm trying to figure out a basic formula to count the number of tasks coming due within 30 days, but excluding those tasks that are already complete. The formula I'm using is:

=COUNTIFS({Due Date}, >=TODAY(30), {Actual Completion %}, <> 1)

I've also tried:

=COUNTIFS({Due Date}@row, >=TODAY(30), {Owner Completion %}@row, <>1) --> returns #UNPARSEABLE

I'm using the Due Date column and Actual Completion % column (or at least I'm trying to) from my grid plan, to drive my count results in my metrics resource sheet

1: Plan

2: Metrics sheet

My guess is that I'm using the wrong bracket type in the formula...{ versus [, or it's the wrong formula altogether. Is there a better or more efficient (stable) formula to use based on the columns I have set up?

Thanks for the help!

Javed Hassan

Hello!

A couple of thoughts:

1. First, I think you would want to use <=Today(30) as those are the tasks due in 30d or less. Your current formula would return tasks due in 30 days or further.
2. Remove the @row from your formula. You shouldn't need this when referencing another range.

Hope this helps!

Paul Newcome

Yes. The first COUNTIFS should work if you swap the "greater than" out for a "less than".

bentlb3

Thank you Javed and Paul. The solution is partially working. It looks like the issue is with this: {Actual Completion %}, <>1

I'm using this formula below, with the due date column selected as my Range 1. If I remove {Actual Completion %}, <> 1, then I get a return. But adding it back in gives me #INVALID REF. The data format in the {Actual Completion %} column cells is in "%" format. Is there a chance the formula doesn't like %?

=COUNTIFS({Due Date}, >=TODAY(30), {Actual Completion %}, <> 1)

Thank you!

bentlb3

Correction: I changed the greater than, to less than already. Then had the issue still persist as described above. Here's my current formula:

=COUNTIFS({Due Date}, <=TODAY(30), {Actual Completion %}, <> 1)

Also, I'm referencing another column in another sheet, if that additional info helps.

Thank you!

Brad

Paul Newcome

HOw is the data in the other sheet being populated?

bentlb3

I am manually entering the finish dates when tasks are completed, then the actual percentage is auto calculated from the owner percentage (which is manually entered).

Example below:

Complete date = manually entered

Owner Completion % = manually entered

Actual Completion % = auto calculated from "Owner Completion %" field

The fields I'm referring to in my formula is looking at the "Due Date" field (manually entered), and the "Actual Completion %" field.

Hope this helps!

Paul Newcome

Is the Due Date column set as an actual date type column? What is the formula in the Actual % Complete Column? When you enter data into the Owner % Complete column, are you having to manually input the % sign or are you able to just enter a number and the % sign is automatically added to it?

bentlb3

1. Is the Due Date column set as an actual date type column? YES
2. What is the formula in the Actual % Complete Column?
   1. =IF([In Scope?]@row = 0, "", IF([Start Date]@row = "", "", IF($C@row > 0, AVG(CHILDREN()), IF([Owner Completion %]@row = "", 0, [Owner Completion %]@row))))

1. When you enter data into the Owner % Complete column, are you having to manually input the % sign or are you able to just enter a number and the % sign is automatically added to it?
   1. the % sign is automatically added to it

bentlb3

Would it make a difference if I switched to the thousands separator, to overcome any system issues with the "%" sign?

Paul Newcome

Lets run some tests on the source data...

Insert a temporary text/number column next to both % Completes and use

=IF(ISNUMBER([Owner Completion %]@row), "", "TEXT")

Do the same for the other % Complete. Are there any rows that contain "TEXT" in the temp columns?

Same logic for the date. Insert a temporary text/number column and use

=IF(ISDATE([Due Date]@row), "", "TEXT")

Any rows with "TEXT" in them?

bentlb3

No rows with "TEXT" in any of the 3 columns. I did notice that the if I removed the value in the cell, then it would return a "TEXT" value in the corresponding TEMP column.

Paul Newcome

Removing the value and getting "TEXT" in the temp column is to be expected.

I just now saw the error you posted before. Invalid Reference means the cross sheet reference was not set up properly. Ensure that you have those both set up properly (clicking on the appropriate column header and whatnot) and see if that clears it up. I was working based on the (obviously incorrect) thought that you were just getting a zero as a result.