Need help with Countifs

Sean Maney
Sean Maney ✭✭✭✭
edited 06/07/23 in Formulas and Functions

Wrote this formula but its not working:

=COUNTIFS(Stage:Stage, "Active - Low. Probability", [R2 Offer Submitted Date]:[R2 Offer Submitted Date], >=[Bid Due Date]:[Bid Due Date])

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    Try using [Bid Due Date]@row instead of the whole column.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @Sean Maney

    Could you us a helper column that does that logic for you.

    Helper =IF([R2 Offer Submitted Date]@row >=[Bid Due Date]@row, "Yes", "No")

    Then you can use =COUNTIFS(Stage:Stage, "Active - Low. Probability", Helper:Helper, "Yes")

  • Sean Maney
    Sean Maney ✭✭✭✭

    The helper column worked, thanks.

    R2 Submitted Date > BDD is the helper column: =IF([R2 Offer Submitted Date]@row > [Bid Due Date]@row, "Yes", "No")

    This is working: =COUNTIFS(Stage:Stage, "Active - High Probability", [R2 Submitted Date > BDD]:[R2 Submitted Date > BDD], "Yes")

    Now I need to add to the COUNTIFS if the [R2 Submitted Date] is blank and TODAY is >[Bid Due Date]

    Thanks for your help

  • Sean Maney
    Sean Maney ✭✭✭✭

    Why doesn't this work?

    =COUNTIFS(Stage:Stage, "Active - High Probability", [R2 Submitted Date]@row, > [Bid Due Date]@row)

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @Sean Maney That doesn't work because you [R2 Submitted Date]@row is not a range, you need to change it to [R2 Submitted Date]:[R2 Submitted Date].

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!