COUNTIF not working against Modified date

DKazatsky2

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

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.