COUNTIFS Using a Date Column

Richard King

Hi,

I have a sheet from where I simply need to count Total tasks, Total tasks not complete in date based on data in other columns including a date column. The date column is compared to a date in a cell in the Metric sheet; and should return a value based on whether the date in the column row is within or outside of the date in the Metric sheet.

I can get it to work in one formula that is counting all tasks not exceeded a due date, based on the "Report End Date", as below.

=COUNTIFS({IMPORT SHEET Range 1}, =$[Primary Column]@row, {IMPORT SHEET Range 2}, ="PPM", {IMPORT SHEET Range 7}, <=[Report End Date]@row, {IMPORT SHEET Range 5}, <>"CANCELLED")

The formula below is essentially doing the same as above, but just looking for open tasks (not completed) but the due date is after the date in the Metric sheet cell "Report End Date".

=COUNTIFS({IMPORT SHEET Range 1}, =$[Primary Column]@row, {IMPORT SHEET Range 2}, ="PPM", {IMPORT SHEET Range 6}, ISBLANK(@cell), {IMPORT SHEET Range 7}, >=[Report End Date]@row)

This formula is not working correctly and is counting all open tasks and appearing not to be limited by the "Report End Date". IMPORT SHEET Range 7 is the date column in the sheet being counted.

Unfortunately I can't share the sheet.

Any thoughts would be appreciated.

Thanks

Richard

Maaik Meijerink

Hello Richard,

If sharing the sheet is not possible, could you make a copy of both sheets and share only limited rows.

I am sure it will help getting a better understanding what data you have and what you want to achieve.

Grtz, Maaik

Maaik Meijerink

Hello Richard,

If sharing the sheet is not possible, could you make a copy of both sheets and share only limited rows.

I am sure it will help getting a better understanding what data you have and what you want to achieve.

Grtz, Maaik

Richard King

Hi Maaik,

I'm not sure how or why, the formula has started to work. All I did was delete an equals sign and put it back.

Regards

Richard