I am trying to count only BTAMS received column that are dated with in 15 day of schedule column

Michael Bowers

I need to count only "B received column" that are dated with in 15 day of "schedule column" but if BOTH dates are in the past of today I don't want to see anything. This what I have so far. What am I missing? I can't even get the first part to function.

=COUNTIFS([B Received from WF]@row, >=Schedule@row(15))

Genevieve P.

Hi @Michael Bowers

If you want to look within a whole column, you'll want to reference the column like this:

[B Received from WF]:[B Received from WF]

...instead of using [B Received from WF]@row, which would only look at one cell in this row. Then to add 15 days on to a date, you'll need to use the + indicator instead of (this):

Schedule@row + 15

So it looks like you may need two things:

• one formula in a helper column to check to see if both dates in in the past (and to do nothing if that's the case), as well as comparing the two dates together.
• one total formula to create your count/calculation based off of this helper column

I would personally have this helper column (your "15 day window" column) be a checkbox one, as that will be a fairly easy way to indicate if each individual row matches your criteria or not.

Try this formula:

=IF(AND([B Received from WF]@row < TODAY(), Schedule@row < TODAY()), 0, IF([B Received from WF]@row >=Schedule@row + 15, 1, 0))

Keep in mind this will only check the box if the date in your B Received from WF column is greater than 15 days past the Schedule date. Is that what you wanted?

Second, summary formula:

Then you can do a count of those checkboxes:

=COUNTIF([15 day window]:[15 day window], 1)

Let me know if this works for you!

Cheers,

Genevieve