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

Options

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))


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!