COUNTIF from Reference within the month

PBspd

This should be simple, don't know where I've gone wrong.

I am referencing a date column range on a data log.

Created a new sheet as a summary with date min/max columns.

The formula should be looking for a total count of data entries from the sheet, by month.

=COUNTIF({Delayed Scope Reprocessing Log Range 1}, AND(@cell >= [Date Range (Min)]@row, @cell <= [Date Range (Max)]@row))

Carson Penticuff

So, a few things:

First, when you are testing more than one condition, you can use COUNTIFS instead of COUNTIF. This avoids having to nest the AND condition. If we apply this to your situation, we would come up with:

=COUNTIFS({Delayed Scope Reprocessing Log Range 1}, @cell >= [Date Range (Min)]@row, {Delayed Scope Reprocessing Log Range 1}, @cell <= [Date Range (Max)]@row)

The downside here, is you can run into issues if there are blank rows (even if they are at the bottom) of your source data, or if something slips in that is not correctly formatted as a date.

What I would do is avoid the "helper" columns for min and max dates entirely and use your Month column for comparison. This also builds in some IFERRORs that will be necessary to error proof your source data:

=COUNTIFS({Delayed Scope Reprocessing Log Range 1}, IFERROR(YEAR(@cell), 1) = VALUE(LEFT(Month@row, 4)), {Delayed Scope Reprocessing Log Range 1}, IFERROR(MONTH(@cell), 1) = VALUE(RIGHT(Month@row, 2)))

Carson Penticuff

So, a few things:

First, when you are testing more than one condition, you can use COUNTIFS instead of COUNTIF. This avoids having to nest the AND condition. If we apply this to your situation, we would come up with:

=COUNTIFS({Delayed Scope Reprocessing Log Range 1}, @cell >= [Date Range (Min)]@row, {Delayed Scope Reprocessing Log Range 1}, @cell <= [Date Range (Max)]@row)

The downside here, is you can run into issues if there are blank rows (even if they are at the bottom) of your source data, or if something slips in that is not correctly formatted as a date.

What I would do is avoid the "helper" columns for min and max dates entirely and use your Month column for comparison. This also builds in some IFERRORs that will be necessary to error proof your source data:

=COUNTIFS({Delayed Scope Reprocessing Log Range 1}, IFERROR(YEAR(@cell), 1) = VALUE(LEFT(Month@row, 4)), {Delayed Scope Reprocessing Log Range 1}, IFERROR(MONTH(@cell), 1) = VALUE(RIGHT(Month@row, 2)))