COUNTIFS Using a Date Column

Options

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

Best Answer

  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭
    Answer ✓
    Options

    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
    Richard King ✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!