COUNTIF not working against Modified date

Hi all,

A colleague of mine has come to me with an issue and I am finding myself as stumped as he is. We are trying to collect metrics based off a modified date field - one metric for before a certain date, one metric for after. One of the metrics seems to be counting something additional and we can't determine why?

When we set a filter on the main sheet, we get 1 row, which is what is expected - see screen shots.

When we try to use a formula, it is returning a result of 2 - see screen shots.

Similarly, the other metric seems to be under reporting by 1. The assumption is the error is the same for both.

If anyone has an idea of why this is happening, it would be greatly appreciated.

Thanks,

Dave

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @DKazatsky2 - I would troubleshoot by seeing what the COUNTIF formula is pulling in. You can do that with the JOIN(COLLECT) functions. That should read like this:

    =JOIN(COLLECT({Modified Date},{Modified Date}, @cell>=DATE(2024,5,23)), " ")

    This should pull in the actual results (with a space between them), instead of just the count.

    I sometimes get a little annoyed with the native Modified Date and create a helper column that points to it and pulls the date. Such as:

    =DATE(YEAR([Modified Date]@row), MONTH([Modified Date]@row), DAY([Modified Date]@row))

    Then I use this helper column as my reference when I'm doing cross-sheet references. Overkill? Maybe.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!